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
 
Re: Is there a way (VBA) Sekection in Array

The cell either contains a value or not, blanks are just that (not hidden nulls or formulas)

I ran the updated code several times and (blanks) where still in the list along with the broken values, but not a single date value was selected, so all good apart from the (blanks)
Try this :
Code:
Dim notDate() As String, cel As Range, i%
i = 0
ReDim notDate(0)
For Each cel In [U2:U6699]
    If Not IsDate(cel) And Not IsEmpty(cel) Then
        notDate(i) = cel
        i = i + 1
        ReDim Preserve notDate(i)
    End If
Next
[COLOR=#ff0000]notDate(i) = "~<>"[/COLOR]
ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=21, Criteria1:=notDate, Operator:=xlFilterValues
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Re: Is there a way (VBA) Sekection in Array

Try this :
Code:
Dim notDate() As String, cel As Range, i%
i = 0
ReDim notDate(0)
For Each cel In [U2:U6699]
    If Not IsDate(cel) And Not IsEmpty(cel) Then
        notDate(i) = cel
        i = i + 1
        ReDim Preserve notDate(i)
    End If
Next
[COLOR=#ff0000]notDate(i) = "~<>"[/COLOR]
ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=21, Criteria1:=notDate, Operator:=xlFilterValues

That got the Blanks off, thank you very much.

I was tweaking the code I was running, I added -X- as a potential output, could that be eliminated in code would notDate(i) = "~<>" OR notDate(i) = "~-X-" work (I did try a couple of variants' on that theme)
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

IRONIC
notDate(i) = "~-X-" seems to stop the blanks being selected
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

I'm not sure why you consider a helper column is not messy.
Because it's simply a single column which doesn't affect the data and can easily be added/deleted with a few lines of code.:)
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

Because it's simply a single column which doesn't affect the data and can easily be added/deleted with a few lines of code.:)

Repeat (for third time):
You are assuming that there is no manual manipulation of the data after the filter and before the helper column can be deleted.

I would still like to see your suggestion for a worksheet formula to be put in the helper column.
I have yet to see a formula that satisfactorily checks for dates.
 
Last edited:
Upvote 0
Re: Is there a way (VBA) Sekection in Array

@Norie @footoo
gents, please don't fall out over a technicality on my behalf
If you take into account what the OP (me) wanted. My preference was for no helper column
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

That got the Blanks off, thank you very much.

I was tweaking the code I was running, I added -X- as a potential output, could that be eliminated in code would notDate(i) = "~<>" OR notDate(i) = "~-X-" work (I did try a couple of variants' on that theme)

Code:
Dim notDate() As String, cel As Range, i%
i = 0
ReDim notDate(0)
For Each cel In [U2:U6699]
    If Not IsDate(cel) And Not IsEmpty(cel) [COLOR=#ff0000]And cel <> "-X-"[/COLOR] Then
        notDate(i) = cel
        i = i + 1
        ReDim Preserve notDate(i)
    End If
Next
notDate(i) = "~<>"
ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=21, Criteria1:=notDate, Operator:=xlFilterValues
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

Hey Mole,

Does that mean that Footoo wins the prize?

Cheerio,
vcoolio.
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

I think Mole said a seven night stay at Buckingham Palace!;)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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