Is there a way (VBA) Selection in Array

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 .
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top