Delete results of Autofilter???

  • Thread starter Thread starter Kobayashi
  • Start date Start date
K

Kobayashi

I've searched the site but have been unable to find an answer to what
thought would be a fairly common problem?

I wish to delete all of the rows returned by an autofilter? I can d
this for blank rows and can also create a loop to do this but a
stumped by what I thought would be the quickest, and easiest method?

As always, your help is very much appreciated!

Regards,

Adria
 
Dim rng as Range
set rng = Activesheet.Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
rng.EntireRow.Delete
 
Tom,

Many thanks indeed, for both your advice and prompt response!

I'm off home shortly but can't wait to test this first thing in the
morning. I'll let you know how I get on?

I did try using the offset and resize method but couldn't get the
resize part to work. No doubt I'll have more success now, thanks!

Since I posted this, out of frustration, I have created the
functionality using a loop. Can I ask you what your preferred option
is? Autofilter or loop?

Many thanks once again and I'm honoured to have finally received a
response from your good self!

Have a nice evening!

Adrian
 
Autofilter would be faster in most cases I would think and the code is
probably shorter.

Make sure you test on a copy of your data since information is being
removed.
 
Tom,

Many thanks indeed! Works very well! Simple but very effective.

This does indeed speed the code up and also cuts down greatly on th
amount of code that is needed, especially for the less experienced o
us.
I don't understand why Autofilter's don't seem to be used more in plac
of loops for simple filtering??? I know I'll certainly be looking t
see if I can use the Autofilter instead of a loop in future!

Again, many thanks for your help!

Now, if you could only help me with my 'Lookup/like values' problem!!
;-)

All the best,:
 
Back
Top