VBA Code Autofilter criteria and row deletion not giving result

not the way i would do it

i rejigged a version i had previously into this test data
two sheets before and after

Please Login or Register  to view this content.

@humdingaling, thanks for your response.

Only suggestion I seek from you, and other valued members, is that you are using -

With
End With statement for Looping, together with ACTIVESHEET

but I have similar criteria in different (say 8-10) columns, and there are multiple sheets and such critieria. Any alternative to speedup

and I am specifying sheet name like

Please Login or Register  to view this content.

So, is there any way to shortern your suggestion? You are also creating variable on Column Q

Further, I dont want any message / alert to appear, but simply macro do its job, as there could be many such instances of deleting rows

Thanks for your time, and hope, i made my point clear.

@humdingaling

I modified your code, as under, to suit my purpose, and it does the job. Thanks Brother/Sir.

Please Login or Register  to view this content.

One more suggestion that i seek from you, now, is that, currently we are specifying Column A:Q, so, instead of restricting data filter upto column Q, how do we let excel auto decide filled column on Right side, say sometime A:Z, A:Q, or A:AB and so on, depending on data content in each sheet?

i used A:Q as an example, you can use A:AB for your filter range
ie make it as large as you possible can make it
it will still refer to field 17 (as long as A is your first column)

or you can use a method to find the last column to define your specific range (see below URL on how this can be done)

the lROW was used in this example as i knew that field 17 ie "Q"
it doesnt have to be Q if your data is setup different
you can use other ways to identify your last row
its just without actual data i had to make an assumption


http://www.rondebruin.nl/win/s9/win005.htm
you can go go here to find out various method of last column/Row and see which one works for you