CODEWITHSUNDEEP
excelvba
wildcat89
wildcat89

Reputation: 1285

How to delete multiple rows if value is found in string?

I'm trying to delete multiple rows, if a particular word ("Bottom") is found in a cell. My data in column A looks like this:

OMC
Hold
Value
Bottom 13%
Advertising and Marketing
WPP
Sell
Momentum | B VGM
Bottom 13%
Advertising and Marketing
AIR
Sell
Growth
Top 9%
Aerospace - Defense Equipment
(...)

...so what I'm looking to do is start at A4, and if the cell contains the word "Bottom", it needs to delete cells A1, A2, A3, A4 and A5.

I've tried the following code:

Sub DeleteRow()

Dim N As Long, i As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
For i = 4 To N
ActiveSheet.Cells(i, 1).Select
If InStr(1, ActiveCell.Value, "Bottom", vbTextCompare) <> 0 Then
ActiveCell.Offset(-3, 0).Delete
ActiveCell.Offset(-3, 0).Delete
ActiveCell.Offset(-3, 0).Delete
ActiveCell.Offset(-3, 0).Delete
ActiveCell.Offset(-3, 0).Delete 


ElseIf InStr(1, ActiveCell.Value, "Bottom", vbTextCompare) = 0 Then
i = i + 4
End If
Next i

End Sub

...basically attempting to start at the top of the column, check if cell A4 contains the word Bottom, if it does, it'll delete cell A1, which shifts the the data up, so it deletes A1 again and again until the next ticker is in cell A1. But now I want it to start at A4 again and then repeat the process. If the new value in A4 doesn't contain "Bottom" (ie. if it says "Top" or anything else), then it needs to jump down to the next row and check there. Always starting again at cell A4 so that nothing gets missed.

I'm essentially trying to just end up with all the proper data that doesn't contain "Bottom", with the example above leaving me with:

AIR
Sell
Growth
Top 9%
Aerospace - Defense Equipment
(...)

...I just don't know how to handle when the rows get deleted and shifted up.

I saw a potential solution here:

Excel VBA Delete Rows

but didn't know how to implement.

Upvotes: 0

Views: 901

Answers (3)

onorio
onorio

Reputation: 26

Try this code instead.

This code using Find method to get cells that contain the word 'bottom', if found then Delete the cells from 3 rows above it till 1 row below it.

Sub DeleteRow()
  Dim R As Range, cellsToDel As String
  'looking for cells that contain the word 'bottom'
  Set R = ActiveSheet.Range("A:A").Find("Bottom", LookIn:=xlValues)
  'if found
  Do While Not R Is Nothing
    'get cells address to be deleted
    cellsToDel = "A" & R.Row - 3 & ":A" & R.Row + 1
    'delete the cells
    ActiveSheet.Range(cellsToDel).Delete xlShiftUp
    'looking for cells that contain the word 'bottom' again
    Set R = ActiveSheet.Range("A:A").Find("Bottom", LookIn:=xlValues)
  Loop
End Sub

Reference: Excel VBA reference

Upvotes: 1

Roy Brander
Roy Brander

Reputation: 121

If I've got your problem correctly from the example, you really need to delete everything above the LAST "Bottom" line in your data - plus one more row.

Excel has a powerful function for doing this search, in the Find method of a Range:


Dim SearchData as Range, LastBottom as Range
Set SearchData = Range("A1",Range("A1").End(xlDown))  ' assumes no blank lines



Set LastBottom= SearchData.Find("Bottom ",SearchDirection:=xlPrevious)
If LastBottom IS Nothing Then 
    MsgBox "Couldn't find it"
Else
    Range(Rows(1),Rows(LastBottom.Row+1).Delete
End if

Remarks: As the comment notes, I assume the data has no blank lines when I use "End(xlDown)" to find the end of the data, rather than "xlUp" from the last row of the spreadsheet.

So you see the Find function, with the "xlPrev" search direction going up the range from the bottom, finding the last "Bottom" cell.

If you want to delete whole rows, use the Row() function, or for multiple, use Range(Rows(X),Rows(Y)) to then run the ".Delete" method on, and the whole lot of them are gone in one command.

Upvotes: 0

Error 1004
Error 1004

Reputation: 8220

Try:

Option Explicit

Sub test()

    Dim LastRow As Long, i As Long

    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .cells(.Rows.Count, "A").End(xlUp).Row

        For i = LastRow - 1 To 1 Step -5

            If InStr(.Range("A" & i).Value, "Bottom") <> 0 Then
                .Rows(i + 1).Delete
                .Rows(i).Delete
                .Rows(i - 1).Delete
                .Rows(i - 2).Delete
                .Rows(i - 3).Delete
            End If

        Next i

    End With

End Sub

Upvotes: 0

Related Questions