Macro: Find and delete entire row where value = X

scrtchmstj · #1 (**permalink**) 01-19-11, 11:56

I don't have a lot of experience with VBA on the Excel side. I used the record function to try to record an action but it appears to not be recording all my activity. Basically, I want to delete the entire row for any row where the value in column B is "X". It looks like the record function omitted any "Find" activity. Here's the code it spits out. When I run again, it gets rid of every column.

Code:

    Columns("B:B").Select
    Selection.EntireRow.Delete

Colin Legg · #2 (**permalink**) 01-19-11, 12:11

Hi,

Have a look at this article on how to delete rows in Excel:
Excel VBA: How To Delete Rows - Xtreme Visual Basic Talk

Post #4 does almost exactly what you want except that you'll have to change:

The column it is searching
The value is is searching for

Let us know if you have any problems...

scrtchmstj · #3 (**permalink**) 01-19-11, 12:41

Hmm, it didn't seem to do anything. Here's the code I tried. - Josh

Code:

Sub Example1()

    Const strTOFIND As String = "X"

    Dim rngFound As Range, rngToDelete As Range
    Dim strFirstAddress As String
    
    Application.ScreenUpdating = False
    
    With Sheet1.Range("B:B")
        Set rngFound = .Find( _
                            What:=strTOFIND, _ 'changed to strTOFIND from sTOFIND
                            Lookat:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=True)
        
        If Not rngFound Is Nothing Then
            Set rngToDelete = rngFound

            'note the address of the first found cell so we know where we started.
            strFirstAddress = rngFound.Address
            
            Set rngFound = .FindNext(After:=rngFound)
            
            Do Until rngFound.Address = sFirstAddress
                Set rngToDelete = Application.Union(rngToDelete, rngFound)
                Set rngFound = .FindNext(After:=rngFound)
            Loop
        End If
    End With
    
    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
    
    Application.ScreenUpdating = True

End Sub

Colin Legg · #4 (**permalink**) 01-19-11, 12:44

Code:

With Sheet1.Range("B:B")

Check these two things:

Is Sheet1 the codename of the sheet that you want to search on?
Is the code contained in the same workbook as the sheet you want to search on?

scrtchmstj · #5 (**permalink**) 01-31-11, 11:21

Colin,

I sort of got things working with this different code but still having some issues. Here is the code I'm currently running. When I run this, it will scrub down through each row looking for the value X in column B. However, it tends to miss rows when they are adjacent to another row with an X value. For example. If rows 1 thru 5 are X,null,X,X,X, it will delete rows 1, 3 and 5 but leave row 4. Is there any way to build a range so that it goes through adding to the selected range and then at the end I could delete all rows at once? Or is there a simpler way?

Code:

Sub Delete_All_Xed_Rows()
    
    Dim SearchRange As Range
    Dim FindWhat As Variant
    Dim FoundCells As Range
    Dim FoundCell As Range
    
    Set SearchRange = Range("B:B")
    FindWhat = "X"
    Set FoundCells = FindAll(SearchRange:=SearchRange, _
                            FindWhat:=FindWhat, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByColumns, _
                            MatchCase:=False, _
                            BeginsWith:=vbNullString, _
                            EndsWith:=vbNullString, _
                            BeginEndCompare:=vbTextCompare)
    If FoundCells Is Nothing Then
        Debug.Print "Value Not Found"
    Else
        For Each FoundCell In FoundCells
            'THIS SELECTS THE CELL FOUND AND DELETES THE ENTIRE ROW
            Range(FoundCell.Address(False, False)).Select
            Selection.EntireRow.Delete
        Next FoundCell
    End If

End Sub

Colin Legg · #6 (**permalink**) 01-31-11, 15:55

Hi,

Hard to say exactly what the problem is because FindAll() is a custom function and I don't know how it works. However, the code you posted in #3 looks perfect subject to the points I made in post #4. That code finds all the cells that need to be deleted and then deleted them all in one go, rather than deleting them one at a time. Attempts to delete cells one at a time often exhibit the problem you have in yours because when a cell is deleted, the remaining cells are shifted up (or to the left) which means that they might be missed in a loop. Deleting them all in one go avoids this issue and is also more efficient because the calculation tree only needs to be rebuilt once. If you are still having trouble with the original code then I would be happy to look at your project if you attach it to the thread?

Hope that helps...

scrtchmstj · #7 (**permalink**) 02-08-11, 10:46

Colin, Thanks for your help on this. I resorted to a simpler code because I will not be sure of the workbook name because it will be created and pasted into and then this code will run to delete entire rows from it, then it will minimize the spreadsheet, go to the main spreadsheet, run a different number in a range, copy and paste to new spreadsheet,...etc.

Here's the simplest code I found to delete the entire row when the column B value is "X"

Code:

'THIS DELETES ALL ROWS WHERE THE VALUE IN COLUMN B IS "X"
    Dim FoundCell As Range
    Application.ScreenUpdating = False
    Set FoundCell = Range("B:B").Find(what:="X")
    Do Until FoundCell Is Nothing
        FoundCell.EntireRow.Delete
        Set FoundCell = Range("B:B").FindNext
    Loop

Colin Legg · #8 (**permalink**) 02-08-11, 18:02

Hi,

Well done on producing that code and thanks for posting back your solution. It's not as efficient as the code referenced in post #2 (even though it is shorter) but I can definitely see that it's simpler!

Cheers,

Internet Services

Web Development

Digital Marketing

Consumer Tech