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

Footloo

How would it be complicated to add a formula in a column and use that to filter?

It's basically what you are doing without the loop.
Complicates it by adding a helper column.
Also, what formula would you use - presumably a UDF?
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Re: Is there a way (VBA) Sekection in Array

I'd love to say this is working, I can't see for the life of me why it isn't
In what way is it not working?
Are the blank cells really blank?
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

footoo

Why would it be a UDF?

Also, how does adding a temporary, helper column complicate things?
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

footoo

Why would it be a UDF?

Also, how does adding a temporary, helper column complicate things?

What formula would you suggest?

Would the helper column not have to remain on the sheet while the filter is in place?
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

What formula would you suggest?
Not 100% sure but if I recall correctly the criteria, and based on some of the posted code so far, it would be a simple logic formula to check if column U had a date and wasn't empty.

=AND(ISNUMBER(U2), U2<>"") *


footoo said:
Would the helper column not have to remain on the sheet while the filter is in place?
Well yes, but as soon as the data was filtered and whatever required data was extracted/deleted then the 'helper' column could be removed.

PS If we were to go through the data row by row why not dump it into an array, loop through that array checking the criteria and output the required result to another array.

* I realise that's a pretty simplistic formula and using ISNUMBER isn't really a sufficient check to see if a value is a date but it could be expanded.
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

Not 100% sure but if I recall correctly the criteria, and based on some of the posted code so far, it would be a simple logic formula to check if column U had a date and wasn't empty.

=AND(ISNUMBER(U2), U2<>"") *



Well yes, but as soon as the data was filtered and whatever required data was extracted/deleted then the 'helper' column could be removed.

PS If we were to go through the data row by row why not dump it into an array, loop through that array checking the criteria and output the required result to another array.

* I realise that's a pretty simplistic formula and using ISNUMBER isn't really a sufficient check to see if a value is a date but it could be expanded.

* I realise that's a pretty simplistic formula and using ISNUMBER isn't really a sufficient check to see if a value is a date but it could be expanded
A UDF would be the simplest way. But then, instead, it would be better to avoid the helper column.
I would be interested to see what expanded worksheet formula you would suggest.

Well yes, but as soon as the data was filtered and whatever required data was extracted/deleted then the 'helper' column could be removed.
Makes it all a bit messy, doesn't it? (particularly if the filtered data is to be amended manually)

PS If we were to go through the data row by row why not dump it into an array, loop through that array checking the criteria and output the required result to another array.
Agreed. But the desire to avoid worksheet object loops quite often approaches OCD behaviour even when the run-time saving is immaterial.
 
Last edited:
Upvote 0
Re: Is there a way (VBA) Sekection in Array

Still not sure why you think using a helper column would be messy.

The column, with formulas, could be added with one line of code then you would have the filter to get the required data followed by code to copy/delete and finally one line of code to delete the helper column.
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

@footoo @Norie @vcoolio

Thank you all for taking so much interest in this problem.

My decision was to use no helper column as I think a simple loop in the column would surfice

I have a stand alone bas file to apply the formatting highlights I want, so additions could be added to it (you know how it is, you have an idea on the way you expect something to work, so that becomes the intent)

The SQL provides a single output, that apart from highlights for data inconsistencies (bas file) is ready to use. There will be no adjustment in the output (that is a requirement for the checker to update the programme that holds the staff data so it is right at source)
 
Upvote 0
Re: Is there a way (VBA) Sekection in Array

In what way is it not working?
Are the blank cells really blank?

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

Still not sure why you think using a helper column would be messy.

The column, with formulas, could be added with one line of code then you would have the filter to get the required data followed by code to copy/delete and finally one line of code to delete the helper column.

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 guess that the OP wants the filter so that manual amendments can be made to the filtered data.

I'm not sure why you consider a helper column is not messy.

What is your suggested worksheet formula to be put in the helper column?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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