Delete all hidden rows after filtering

[VBAProIWish]

Hello All,

I currently have this code here that deletes any rows that have "Grape" in the "Fruit" column...

Sub Delete_rows_with_text_x_in_col_x()
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim rCount As Long
With ActiveSheet
    nCol = Application.Match("Fruit", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case .Cells(rCount, nCol).Value
                Case "grape", "GRAPE"
                .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With

But the code seems pretty slow. Is there a way, using the autofilter that this code can be faster?


I was thinking, logically, it could be this way here...

1. Autofilter the entire workbook
2. Filter the fruit column for all values except "grape" or "GRAPE".
3. Delete any rows not visible, which would be any rows that contain "grape" or "GRAPE".

Can this be done via code?

Thanks much

 

[Colin Legg]

Hi,

Why not invert that logic:

1. Autofilter the fruit column for value "grape" (filtering isn't case sensitive).
2. Delete any visible rows (except the column header)

Going for visible rows means that you can use the Range.SpecialCells(xlCellTypeVisible) method.

 

[VBAProIWish]

Hmmm, so how do you delete any visible rows NOT including the header row?

Problem 1 - Note that I tried it by selecting A1 and then going down one cell, but, the only problem is that it went to row T, which will not obviously always the first filtered row I need to select.




Problem 2 - After getting to row T, I used this command here

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select

The reason I had to do it 7 times is because SOMETIMES there are blank values in the cell, but SOMETIMES, there are NOT. So, I would never be able to know the exact amount of times to use that command.

Any thoughts?

 

[Colin Legg]

Problem 1-

Here's one I made earlier:
http://www.xtremevbtalk.com/showpost.php?p=1305670&postcount=4

(There is also a link to it in my signature)

Problem 2-

You could autofilter the whole used range, but the point of using the autofilter is you only need to do it on the Fruit column (filtering hides the entire row). So long as you know where the Fruit column is, you don't have to worry about the other columns.

 

[VBAProIWish]

Colin,

Thanks for the response, but unfortunately, that won't work.

This little bit of code I want is smack in the middle of a very large macro with several modules that can't use filtered data. These rows need to be removed from the face of the earth for this to work.

It's looking like I'll have to stick with my original code unless you or anyone else have any other ideas.

Thanks

 

[Colin Legg]

This little bit of code I want is smack in the middle of a very large macro with several modules that can't use filtered data. These rows need to be removed from the face of the earth for this to work.

This method does completely remove them from the face of the earth. It deletes them. Like I said, you only need to filter by one column and then delete the visible rows (the ENTIRE row).

However, if you can't use the autofilter for structural reasons, such as your spreadsheets not being in a table format, then you can use the Range.Find method which is also demonstrated on the link I gave you. However, given the information in your first post, I strongly suspect that you can use the autofilter.

 

[VBAProIWish]

Woww,

I just tried your first code, with about 400 rows, it stopped responding, I couldn't stop running the macro, and then, I couldn't even stop Excel via task manager. It said "cannot stop Microsoft Excel".

I don't know what happened there.

Also, the first code looks to column A for the value "hello". I need to find my column, not by column position, but by column name.

Any thoughts on what happened with the not responding issue and getting the code to by column name, NOT position?

Thanks

 

[Colin Legg]

Hi,

If you tell me the codename of the sheet and the name of the column (I assume this is a named range) I'll post some adapted code for you.

 

[VBAProIWish]

It would be the activesheet and the column would be "Fruit", but as I said in my last post, the first macro in the post on your other site left Excel unresponsive.

I'm afraid to run it again. Unless maybe it was something I did? Not sure. I only tested it on about 400 rows.

Thanks

 

[Colin Legg]

It would be the activesheet and the column would be "Fruit", but as I said in my last post, the first macro in the post on your other site left Excel unresponsive.

So obviously these aren't named ranges then. To use the autofilter you would have to find the header Fruit and then autofilter that column. To make my life easier I'm just going to paste the Range.Find() method. This will delete any rows in the active worksheet where any cell on that row contains the value "lime" (not case sensitive).

If you want to delete "grape" then change the strTOFIND constant value to "grape" instead of "lime".

<!-- / message -->Sub Example1()
 
    Const strTOFIND As String = "lime"
 
    Dim lngarrCounter As Long
    Dim rngFound As Range, rngToDelete As Range
    Dim strFirstAddress As String
 
    Application.ScreenUpdating = False
 
    With ActiveSheet.UsedRange
        Set rngFound = .Find( _
                            What:=strTOFIND, _
                            Lookat:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
 
        If Not rngFound Is Nothing Then
            strFirstAddress = rngFound.Address
 
            If rngToDelete Is Nothing Then
                Set rngToDelete = rngFound
            Else
                If Application.Intersect(rngToDelete, rngFound.EntireRow) Is Nothing Then
                    Set rngToDelete = Application.Union(rngToDelete, rngFound)
                End If
            End If
 
            Set rngFound = .FindNext(After:=rngFound)
 
            Do Until rngFound.Address = strFirstAddress
                If Application.Intersect(rngToDelete, rngFound.EntireRow) Is Nothing Then
                    Set rngToDelete = Application.Union(rngToDelete, rngFound)
                End If
                Set rngFound = .FindNext(After:=rngFound)
            Loop
        End If
    End With
 
    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
    Application.ScreenUpdating = True
 
End Sub

I'm afraid to run it again.

Your call. Perhaps you should save your work and then run it on a back up.