loop code on cell range

[Otto]

Please help,I am new to code and want to do the following.
I need to delete information on a worksheet which are older than a specified
date and then loop the code through a range up to 500 times.The macro
evaluate the date in cell a3 and if the date are older than than today less
let say 30 days it should then clear contents through range a3:q3 and move to
a4 and so on.
this is the code as i have it at the moment and it works fine except that i
have to repeat the code for each roe in the range.Is there another way to do
this?
Sub Button10_Click()
UserForm2.Show
If Range("r3") >= Range("r1") Then
Range("a3:q3").Select
Selection.ClearContents
Range("a3").Select
ElseIf Range("r3") < Range("r1") Then
End If
Sub Button10_Click()
UserForm2.Show
If Range("r43") >= Range("r1") Then
Range("a4:q4").Select
Selection.ClearContents
Range("a4").Select
ElseIf Range("r4") < Range("r1") Then
End If

 

[JLatham]

Otto,
I'm not sure what your two userforms are doing, but the code below may do
away with the need for them anyhow. You could take the code below and put it
in a regular code module and change the macro assigned to Button10 to this
code for testing (right-click on the button and choose assign macro and just
point it to this macro).

A little explanation: the SET command creates an in-memory reference to the
Range (cell) specified. Much faster to work through a list using this method
than pretty much any other way. So the Set command you see below is creating
a reference to all of the cells in column R beginning at row 3 and going on
down to the last cell in column R with anything in it. The "For Each ...."
loop then works through that list one cell at a time and does the date
comparisons to decide whether or not to clear the contents of a row from A to
Q.

I've defined everything that might change at the start of the module so that
it is easy to make changes to it later on. By simply changing the values
that are defined, you change how it works and what areas it works with. I'm
thinking that perhaps your userforms were somehow letting you enter the
number of days to look back - and you could probably still incorporate that
into the code. I'm just not sure of what is displayed in R1 after you've
used the two userforms.

Sub ClearOldEntries()
'you must have the sheet with the information
'on it selected before running this macro

Const maxDaysOldToKeep = 30 ' keep all that are >= Today() - 30 days
Const firstRow = 3 ' start at row 3
Const firstColToClear = "A"
Const lastColToClear = "Q"
Const datesColumn = "R"
Dim dateList As Range
Dim anyDateInList As Range
Dim testDate As Date

'save the test date for use
testDate = Now() - maxDaysOldToKeep
'get the address of all date entries
Set dateList = Range(datesColumn & firstRow & ":" & _
Range(datesColumn & Rows.Count).End(xlUp).Address)
'work through each of the cells containing dates
'to see if they are after the test date
For Each anyDateInList In dateList
'make sure the entry is actually a date
If IsDate(anyDateInList) Then
If anyDateInList < testDate Then
'it is older than 30 days earlier than today
Range(firstColToClear & anyDateInList.Row & ":" & _
lastColToClear & anyDateInList.Row).ClearContents
End If
End If
Next
End Sub