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

Seamus2626Asked:
Who is Participating?
 
StephenJRCommented:
I tend to use code like this for AutoFilter:
Sub x()

Dim rData As Range

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=7, Criteria1:="MDBKIDQ"
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True

End Sub

Open in new window

0
 
StephenJRCommented:
Seamus - are you asking how to delete all the entries which are filtered?
0
 
Seamus2626Author Commented:
Yes!
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
dlmilleCommented:
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
0
 
Seamus2626Author Commented:
Hey dlmille, that wont work for me, as the data could start at A2 or a11 etc

Thanks Stephen
0
 
dlmilleCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.