Page 1 of 5 123 ... LastLast
Results 1 to 10 of 42

Thread: Is there a way (VBA) Selection in Array

  1. #1
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,150
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Question Is there a way (VBA) Selection in Array

    I have

    ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=21, Criteria1:=Array( _
    "(D/03/4V", "//", "12/95/E1", "15/19/95", "32/-2/09", "32/80/76", "61/40/07", _
    "9E/09/NJ", "C1/No/-L", "GV/E9/N5", "-X-"), Operator:=xlFilterValues

    Is there a way to just use a IS NOT A DATE value in the filter
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Perhaps loop through the data and assign the non-date cells to an array then :
    Code:
    ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=21, Criteria1:=NonDateArray, Operator:=xlFilterValues

  3. #3
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,666
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Quote Originally Posted by mole999 View Post
    Is there a way to just use a IS NOT A DATE value in the filter
    If your dates are the only numbers in the column you could also try.

    Code:
    ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=21, Criteria1:="*", Operator:=xlFilterValues
    [code]your code[/code]

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Posts
    683
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Hello Mole999,

    I had a similar query by a Poster on another forum a few months ago and the following worked for him:-
    Code:
    Sub DeleteStuff()
    
    Dim c As Range
    
    With Sheet1
          For Each c In Range("G2", Range("G" & Rows.Count).End(xlUp))
          If Not IsDate(c.Value) Then
          c.Offset(, 1).Value = 1
          End If
    Next c
    End With
    
    With Sheet1.[A1].CurrentRegion
               .AutoFilter 8, 1, , , 7
               .Offset(1).EntireRow.Delete
               .AutoFilter
    End With
    
    End Sub
    Obviously, the cells had to be formatted correctly and the Poster wanted entire rows (based on Column G) of non date values deleted.
    We placed a value of 1 in the next empty column for non date values and then filtered on 1. Change the ranges and field to suit yourself and see if it works.

    I hope that this helps.

    Cheerio,
    vcoolio.

  5. #5
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,150
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    @vcoolio Thanks, its coming straight out of SQL, so was trying to do it on the fly without many stages. Appreciate the suggestion
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,150
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    some of the values might look like 13/13/1959 so obviously not a valid date, other have bits of text mixed in, but always formatted as **/**/****, and testing the column for less than a number didn't work. I thought I might get away with that
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Code:
    Dim notDate() As String, cel As Range, i%
    i = 0
    ReDim notDate(0)
    For Each cel In [U2:U6699]
    If Not IsDate(cel) Then
        notDate(i) = cel
        i = i + 1
        ReDim Preserve notDate(i)
    End If
    Next
    ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=1, Criteria1:=notDate, Operator:=xlFilterValues

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Posts
    683
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    G'day Mole999,

    I can understand you not wanting to use too many stages.

    As the data is coming straight out of SQL, I'd imagine that the size of the data set would be immense so, I believe, filtering on an array would still be the best option.
    Would it be possible to extract the unwanted items into a list, name that list then pass it to an array to filter. Something along the lines of:-

    Code:
    Dim ar as variant
    
    ar= Sheet2.Range("Mole's List")
    
     For i = 0 To UBound(ar, 1)
             Range("U1", Range("U" & Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
            etc.
            etc.
    I'd reckon that you'd be more than capable of creating some code to extract a list.

    I suppose that, in effect, this would be similar to Footoo's suggestion in post #7 .

    Cheerio,
    vcoolio.

  9. #9
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Quote Originally Posted by vcoolio View Post
    As the data is coming straight out of SQL, I'd imagine that the size of the data set would be immense
    The code in the original post only covers 6,699 rows.

    If FormR's suggestion in post #3 doesn't do it, try the code in post #7 .

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,291
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Why not, instead of looping, use code to add a formula based on the values in the field/column you actually want to filter?

    The formula could determine which rows to keep/delete and you could then filter on this 'helper' column.
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •