Forum Discussion
Solved
Need to run a macro multiple times
Hi. Why does not this code work? I connect the macro to a button and press it, and about half the rows with "1" in col C is deleted. Then I press it again and about 3 more rows disappear. After a few clicks all desired rows are deleted.
Why not all at once - which is what I am trying to achieve? Any tips? Thanks in advance!
_________________________
Sub Delete_Old()
Dim cell As Range
For Each cell In Range("C8:C1000")
If cell.Value = 1 Then
cell.EntireRow.Delete
End If
Next cell
End Sub
____________________________
roboed You have to loop backwards:
Sub Delete_Old() Dim r As Range For r = 1000 To 8 Step -1 If Range("C" & r).Value = 1 Then Range("C" & r).EntireRow.Delete End If Next r End Sub
2 Replies
Sub Delete_Old() Dim i As Integer For i = 8 To 1000 If Cells(i, 3).Value = 1 Then Rows(i).Delete i = i - 1 Else End If Next i End SubYou can try this macro. If row i is deleted the value from row i+1 is moved to row i (and so on). This means that the macro has to check again if row i has the value 1.
roboed You have to loop backwards:
Sub Delete_Old() Dim r As Range For r = 1000 To 8 Step -1 If Range("C" & r).Value = 1 Then Range("C" & r).EntireRow.Delete End If Next r End Sub
