Find a date and clear contents underneath target cell

Question

I have a macro that loops through one column that holds dates from old to more recent. It finds every date that is greater than Now then goes to its adjacent cells and clears them.

I believe it could be much faster if the macro wouldn't loop against all values in the column (500 rows) but instead stops at the first cell match, then goes to its adjacent cells and clears all contents from that row till the bottom of the table (i.e. if the match is at row 15, then clear contents from row 15 all the way down to 500) without checking every row.

    Dim R As Long

    For R = 1 To 500
        If Cells(R, "A").Value >= Now Then Cells(R, "B").Value = ""
        If Cells(R, "A").Value >= Now Then Cells(R, "C").Value = ""
    Next

End Sub 

Answer 1

Using your methodology, if you want to know how to drop out of a loop once the criteria are matched - you would use an Exit For. You also can clear column B and C at the same time, like so:

Dim R As Long
With ActiveWorkbook.Sheets("Sheet1")
    For R = 1 To 500
      If .Cells(R, "A").Value >= Now Then
          .Range("B" & R & ":C500").ClearContents
          Exit For
      End If
    Next
End With

In the above, I've also added reference to the sheet. This is always good practice to prevent possible errors once more than one sheet is available.