Can't get excel macro to return after for loop
I am trying to make a little code that deletes an entire line when certain text is written to a cell.
Sub Delete_Rows()
For Each c In Range("B1:B20").Cells
If c.Value = "text" Then
c.EntireRow.Delete
End If
Next c
End Sub
This is what I wrote in my excel sheet
The problem is when I run it and the condition to delete a cell is met, excel automatically scrolls to the next row without evaluating the current cell, it skips like this
I run it again and only the "text" of the numbers 4 and 9 remains
After I run it for the third time, it finally deletes every cell with text written on it.
I've tried using a while loop instead, offset selection, use an integer to subtract the loop iteration, and a few other ways and no luck.
Any ideas?
+3
source to share
1 answer
There are two options:
- delete from bottom to top or
- delete all matching lines at once (shown below):
Sub Delete_Rows()
Dim deleteRange As Range
For Each c In Range("B1:B20")
If c.Value = "text" Then
If deleteRange Is Nothing Then
Set deleteRange = c
Else
Set deleteRange = Union(c, deleteRange)
End If
End If
Next c
deleteRange.EntireRow.Delete
End Sub
+4
source to share