VBA macro removing rows with 3 conditions

Question

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?

FaneDuru · Accepted Answer · 2022-11-02T08:35:57.413

2

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.

edited Nov 02 '22 at 08:35

answered Nov 02 '22 at 08:30

FaneDuru

38,298
4
19
27

1

This worked like a charm, and very fast! Thanks! – Johanna Nov 02 '22 at 14:57
On my last question, "VBasic2008" wrote that `addToRange` should be `Private Sub addToRange(ByRef rngU As Range, ByVal rng As Range)` making it clear that rngU is being built. – Waleed Nov 04 '22 at 07:30
1

@Waleed 1. It is not mandatory to be `Private`. I made it `Private` only for the fact I am using it in my testing environment in many modules, where I have one where I keep important functions/subs and I want/need having the `Sub` in many places. Otherwise, it will raise an error telling that it already exists, or something meaning that. 2. The default way of passing parameters for any `Sub` is `ByRef`. This means the object address has been passed. In case of `ByVal` a copy of the address is passed. In other words, an object passed `ByRef` is changed in the called `Sub` using it. – FaneDuru Nov 04 '22 at 08:11
1

@Waleed So, `ByRef rngU As Range` is useless, it is anyhow passed in this way and the called `Sub` **does not cange in any way `rng` Range**, so it is also useless using `ByVal rng As Range`. – FaneDuru Nov 04 '22 at 08:13

score 1 · Answer 2 · answered Nov 02 '22 at 08:32

= 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

`=` checks for identical strings, but the OP used asterisks ✳️ (at beginning and ending of string condition `"*--*"`) , So I see that there's no difference between it and using `Like`. — Waleed, Nov 04 '22 at 07:39
I don't understand your comment. `If "x--y" = "*--*" Then` is False, while `If "x--y" like "*--*"` is True — FunThomas, Nov 04 '22 at 07:55

score 0 · Answer 3 · answered Nov 02 '22 at 08:22

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

VBA macro removing rows with 3 conditions

3 Answers3