asked on

delete filtered items

Hi,

I am filtering for a code and deleting in a worksheet, i recorded a macro to do this. I have attached the code.

You can see my problem here is that if i do this tomorrow and there are entries anywhere above 36, it wont delete them.

I cant select the whole sheet and delete as i need the headers

How can i delete all rows based on that selection

Thanks
Seamus

Selection.AutoFilter Field:=7, Criteria1:="MDBKIDQ"
    Rows("36:36").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("D89").Select
    ActiveSheet.ShowAllData
    Range("E4").Select

Open in new window

StephenJR

Seamus - are you asking how to delete all the entries which are filtered?

Seamus2626

ASKER

Yes!

dlmille

Sub clean()

'define selection here
Range("A:G").Select 'or something

'Assuming your data filter starts on row 1, and the dataset has nothing below it, the following should work

Selection.AutoFilter Field:=7, Criteria1:="MDBKIDQ"
Range("A2", Range("G" & Rows.Count)).End(xlUp).Delete
ActiveSheet.ShowAllData
Range("E4").Select
End Sub

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

Hey dlmille, that wont work for me, as the data could start at A2 or a11 etc

Thanks Stephen

dlmille

If I read the solution you accepted, it also makes assumptions about where the data starts. However, StephenJR's use of xlcelltypevisible is superior which didn't just hit me till now.

Dave