Delete results of Autofilter???
 

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

--
Message posted from http://www.ExcelForum.com

Delete results of Autofilter???
 

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

--
Regards,
Tom Ogilvy

"Kobayashi " wrote in message
...
I've searched the site but have been unable to find an answer to what I
thought would be a fairly common problem?

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

As always, your help is very much appreciated!

Regards,

Adrian


---
Message posted from http://www.ExcelForum.com/

Delete results of Autofilter???
 

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


---
Message posted from http://www.ExcelForum.com/

Delete results of Autofilter???
 

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.
--
Regards,
Tom Ogilvy

"Kobayashi " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

Delete results of Autofilter???
 

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,:

--
Message posted from http://www.ExcelForum.com