?
Solved

delete filtered items

Posted on 2011-05-05
6
Medium Priority
?
234 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Seamus2626
  • 2
  • 2
  • 2
6 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 35699604
Seamus - are you asking how to delete all the entries which are filtered?
0
 

Author Comment

by:Seamus2626
ID: 35699630
Yes!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35699678
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 35699685
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
 

Author Closing Comment

by:Seamus2626
ID: 35699752
Hey dlmille, that wont work for me, as the data could start at A2 or a11 etc

Thanks Stephen
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35699784
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question