Is there a way (VBA) Selection in Array

mole999

Moderator
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,211
Office Version
  1. 2016
Platform
  1. 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,489
Office Version
  1. 365
Platform
  1. 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
1,079
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,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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
3,211
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
1,079
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
3,211
Office Version
  1. 2016
Platform
  1. 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
76,278
Office Version
  1. 365
Platform
  1. 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,129,314
Messages
5,635,506
Members
416,861
Latest member
Breadnjam18

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
Top