Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Macro: Find and delete entire row where value = X

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    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

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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?

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    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

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  7. #7
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    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

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •