VBA macro removing rows with 3 conditions-CodePudding

I'm trying to write a macro that removes rows with the condition that the string in the cells in column A contains "--" or "-4" or "" (empty). I'd do it with a normal filter, but that gives me max 2 conditions.

Sub Delete_Rows()
Dim cell As Range
For Each cell In Range("A:A")
If cell.Value = "*--*" Or cell.Value = "*-4*" Then
cell.EntireRow.Delete
End If
Next cell
End Sub

What am I doing wrong?

CodePudding user response：

= checks for identical strings, so unless you have a cell containing "*--*" or "*-4*", the If-clause will never be true. You will have to use the like-operator:

If cell.Value like "*--*" Or cell.Value like "*-4*" Then

Two remarks:
Your code will loop through the whole Excel sheet (which contains 1'048'576 rows) so that will run a very long time. And, even worse, if you add the check for empty cells to delete a row, it will delete one million rows and it would look as if Excel/VBA is frozen. Therefore you need to figure out the last row before you run the code. More on this at Find last used cell in Excel VBA

And you need to be aware the that code will run on the active sheet - the sheet that currently has the focus. You should always specify the sheet (and workbook) where you want to code to work with. Don't go down the path to Select the sheet to make if active. For more details, see How to avoid using Select in Excel VBA

Sub Delete_Rows()
    Dim cell As Range, lastRow As Long
    ' Replace the following line with the workbook you want to work with
    With ThisWorkbook.Sheets(1)
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        For Each cell In .Range("A1:A" & lastRow)
            If cell.Value Like "*--*" Or cell.Value Like "*-4*" Then
                cell.EntireRow.Delete
            End If
        Next cell
    End With
End Sub

CodePudding user response：

You can use the Like operator instead of "=" to perform the comparison. Consider the following the code:

Sub Delete_Rows()


Dim cell As Range

For Each cell In Range("A:A")
    
    If cell.Value Like "*--*" Or cell.Value Like "*-4*" Then
        cell.EntireRow.Delete
    End If
    
    Next cell

End Sub

You can also read more about the like operator here for example: https://www.wallstreetmojo.com/vba-like/

I hope this helps :D

CodePudding user response：

Please, test the next version. It uses an array for iteration and a Union range to delete rows at once, at the end of the code:

Sub Delete_Rows3Cond()
Dim sh As Worksheet, lastR As Long, rngDel As Range, arr, i As Long

Set sh = ActiveSheet
lastR = sh.Range("A" & sh.rows.count).End(xlUp).row

arr = sh.Range("A1:A" & lastR).Value2 'place the range in an array for faster iteration/processing only in memory
For i = 1 To UBound(arr)
    If arr(i, 1) = "" Or arr(i, 1) Like "*--*" Or arr(i, 1) Like "*-4*" Then
        addToRange rngDel, sh.Range("A" & i) 'create the union range
    End If
Next
If Not rngDel Is Nothing Then rngDel.EntireRow.Delete xlUp
End Sub

Private Sub addToRange(rngU As Range, Rng As Range) 'I creates the Union range
    If rngU Is Nothing Then
        Set rngU = Rng
    Else
        Set rngU = Union(rngU, Rng)
    End If
End Sub

Deleting a row at a time, takes a lot of time and you need to process only the range containing data...

Please, send some feedback after testing it.