CODEWITHSUNDEEP
excelvba
Djhans26
Djhans26

Reputation: 79

Clearing Content via a loop

I have a set of data that changes weekly. I am trying to clear out cells that have "UTC" in the cell as this is information I do not need. My VBA is as follows:

Sub Weekly_Payroll()

Range("C:E,J:J,K:K,L:O,Q:Q,R:R").Delete Shift:=xlToLeft

Dim r As Long
Dim lastrow As Long

r = 3
lastrow = Range("H" & Rows.Count).End(xlUp).Row

Do Until r > lastrow
    DoEvents
    If InStr(1, Cells(r, 8), "UTC") > 0 Then
        Cells(r, 8).ClearContents
        lastrow = lastrow - 1
    Else
        r = r + 1
    End If
Loop

Columns("A:H").EntireColumn.AutoFit

        
End Sub

This week my data is from row 3 to row 187. For some reason the loop is only going to row 102. I have wrote a line to find the last cell in the row and it finds the cell in row 187, so I am not sure why it is stopping at row 102.

Upvotes: 0

Views: 54

Answers (1)

FunThomas
FunThomas

Reputation: 29296

As already mentioned in the comment, the logic of your loop is wrong. This can, by the way, easily checked using the debugger, you should learn how to step thru your code.

Assuming that lastrow is 187: In the moment you find a row that you want to clear, you decrease your end variable, so row 187 is never reached. If you have 85 rows where you clear the data, lastRow will be 102 and the loop will stop there.

In your case, use the For-loop:

For r = 3 to lastrow
    DoEvents
    If InStr(1, Cells(r, 8), "UTC") > 0 Then
        Cells(r, 8).ClearContents
    End If
Next r

Upvotes: 1

Related Questions