VBA | Macro to loop through range and delete rows based on criteria

s0up2up

Board Regular
Joined
Jan 30, 2012
Messages
84
Hi Guys,

I am trying to create a function that loops through code and deletes columns based on a selector. This selector is whether the cell.value starts with the number 1 or 2 (both of these are passed as a string).

This code is below.

Code:
Public Sub deleteData(rngColumn As Range, strSelector As String)

Debug.Print rngColumn.Address


Dim rngCell As Range


For Each rngCell In rngColumn
    Debug.Print rngCell.Address
    Debug.Print Left(CStr(rngCell.Value), 1) = strSelector
    
    If Left(CStr(rngCell.Value), 1) = strSelector Then
        rngCell.EntireRow.Delete
    End If
Next


End Sub

The problem that I am having is that whilst the logic works, it doesn't delete the rows as expected, i.e: if I replaced rngCell.EntireRow.Delete with something like rngcell.Offset(0,5).value = "Please Delete This Row" it would totally work.

Note: This is what I am passing though to it.

Code:
Sheets("Macros").Activate    Call deleteData(Range("T1:T25"), "2")
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
This could be part of the problem
that loops through code and deletes columns based on a selector
your objective is to delete columns but your code
Code:
rngCell.EntireRow.Delete
says otherwise. If you can clarify your objective maybe someone can help.
 
Upvote 0
This could be part of the problem

your objective is to delete columns but your code
Code:
rngCell.EntireRow.Delete
says otherwise. If you can clarify your objective maybe someone can help.

Oh whoops... Sorry I totally meant I want to delete the rows.

On a side note, can I amend the original post to correct this?
 
Upvote 0
Or maybe modify the If statement to this.

Code:
If CStr(Left(rngCell.Value, 1)) = strSelector Then
 
Upvote 0
The reason it isn't working is because your For...Each loop isn't staying in sync with the rngColumns variable whenever you delete a column. It's a little counter intuitive because you would expect the for each loop to be grabbing the first cell object, then the second cell object, then the third cell object, but what really happens that it grabs the cell object in the first position, then it grabs the cell object in the second position, then it grabs the cell object in the third position. So if you are in the first position and delete that row, you 'next' into the second position cell, which is now the cell that was in the third position before you deleted the first row.

There are a couple of ways to tackle the problem, but my preferred method is to collect all of the rows in one range object that you want and delete them all at the same time. I have demonstrated that below. Also I changed the predicate in your If statement to use the Like operator instead of Left function so you can use a string longer than one character in the strSelector parameter.

Code:
Public Sub deleteData(rngColumn As Range, strSelector As String)

Dim rngCell As Range
Dim rngToDelete As Range

For Each rngCell In rngColumn
    If rngCell.Value2 Like strSelector + "*" Then
        If rngToDelete Is Nothing Then
            Set rngToDelete = rngCell
        Else
            Set rngToDelete = Union(rngToDelete, rngCell)
        End If
    End If
Next

If Not rngToDelete Is Nothing Then
    rngToDelete.EntireRow.Select
End If

End Sub

If you are interested in knowing another way to tackle the problem, it involves using a For...Loop with a Step - 1 to go backwards and get the cell in rngColumn by index value and delete the rows one at a time. Then your loop will stay in sync while deleting rows. Not how I would do it now, but it's how I used to solve the problem a few years ago.
 
Upvote 0
The reason it isn't working is because your For...Each loop isn't staying in sync with the rngColumns variable whenever you delete a column. It's a little counter intuitive because you would expect the for each loop to be grabbing the first cell object, then the second cell object, then the third cell object, but what really happens that it grabs the cell object in the first position, then it grabs the cell object in the second position, then it grabs the cell object in the third position. So if you are in the first position and delete that row, you 'next' into the second position cell, which is now the cell that was in the third position before you deleted the first row.

Yeah I had just realized this, as it deleted half the entries whilst I was stepping through, and then it dawned on me.

There are a couple of ways to tackle the problem, but my preferred method is to collect all of the rows in one range object that you want and delete them all at the same time. I have demonstrated that below. Also I changed the predicate in your If statement to use the Like operator instead of Left function so you can use a string longer than one character in the strSelector parameter.

Code:
Public Sub deleteData(rngColumn As Range, strSelector As String)

Dim rngCell As Range
Dim rngToDelete As Range

For Each rngCell In rngColumn
    If rngCell.Value2 Like strSelector + "*" Then
        If rngToDelete Is Nothing Then
            Set rngToDelete = rngCell
        Else
            Set rngToDelete = Union(rngToDelete, rngCell)
        End If
    End If
Next

If Not rngToDelete Is Nothing Then
    rngToDelete.EntireRow.Select
End If

End Sub

So for this, I would simply change rngToDelete.EntireRow.Delete to make this one work?

If you are interested in knowing another way to tackle the problem, it involves using a For...Loop with a Step - 1 to go backwards and get the cell in rngColumn by index value and delete the rows one at a time. Then your loop will stay in sync while deleting rows. Not how I would do it now, but it's how I used to solve the problem a few years ago.

Are you able to show me what this would look like?
 
Upvote 0
So for this, I would simply change rngToDelete.EntireRow.Delete to make this one work?
Oops! You're right. I use select when testing what the delete function is going to do before I do it. I forgot to switch it before posting it on here. That is exactly what you should do. :)

Are you able to show me what this would look like?

This will be slower since you are deleting rows one at a time rather than deleting the rows of a large range all at once, but the macro below is an example. I did not put in Select this time. :)

Code:
Sub DeletingUpwards()

Dim rng As Range
Dim i As Long


    Set rng = [A1:A10]
    'Putting 1 through 10 in the Range
    rng.Value2 = Application.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
    
    For i = rng.Cells.Count To 1 Step -1
        If rng.Cells(i).Value2 Mod 2 = 0 Then 'If it's an even number
            rng.Cells(i).EntireRow.Delete
        End If
    Next i
    
End Sub
 
Upvote 0
This will be slower since you are deleting rows one at a time rather than deleting the rows of a large range all at once, but the macro below is an example. I did not put in Select this time. :)

Ah cool,wondered if it was a speed issue. Thanks for coming back and dropping some knowledge. Appreciate it.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top