Is there a way (VBA) Selection in Array

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,299
Office Version
2019, 2016, 2013
Platform
Windows
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,945
Office Version
2016
Platform
Windows
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
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,382
Office Version
365
Platform
Windows
Re: Is there a way (VBA) Sekection in Array

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
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
917
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.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,299
Office Version
2019, 2016, 2013
Platform
Windows
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
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,299
Office Version
2019, 2016, 2013
Platform
Windows
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
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,945
Office Version
2016
Platform
Windows
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
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
917
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. :wink:

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

Cheerio,
vcoolio.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,945
Office Version
2016
Platform
Windows
Re: Is there a way (VBA) Sekection in Array

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 .
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,541
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,782
Messages
5,446,469
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top