Macro or VBA to Clear Contents of Rows By Date (Starting At A5)

[Safari]

Hi,

I have a list which begins in row 5. In column "A" there is a date
followed by data in 9 more columns in that row.

Before Macro:

Date Comment

06-01-09 (cell A5) Expired (cell B5)
07-01-09 (cell A6) Expired (cell B6)

I want a macro or VBA code that will take a range (say A5:J500) and
clear the contents of all rows (starting in row 5) which have a column
A: date > 06-30-09 (if I could use a cell reference for this date it
would be even better).

After Macro:

Date Comment

06-01-09 (cell A5) Expired (cell B5)

I don't want to record a filter and use that in a macro because a
filter hides the rows with data. This causes a problem as I have
another routine that copies into those rows when the spreadsheet is
updated and then performs a sort, I need to have all rows visible.

 

[Safari]

Please don't reply, I figured it out (didn't have much time and one
problem was fixed by repeating a step):

'Clear contents of monthly data for refreshing
Range("A5:J1000").Select
Selection.ClearContents



'Copy data from all worksheets starting in cell A5 to master tab
Dim sht As Worksheet
Dim target As Worksheet
Dim rng As Range
Dim targetrng As Range

'You can change "Master" to your exact merge sheet name (tab
name for the sheet)
Set target = ActiveWorkbook.Worksheets("Master")
For Each sht In ActiveWorkbook.Worksheets
If Not sht.Name = target.Name Then
'Set rng = sht.UsedRange
Set rng = sht.Range("a5:J500")
Set targetrng = target.Cells(65536, 1).End(xlUp).Offset(5)
rng.Copy targetrng
End If
Next sht

Range("A5:J500").Select



'Run sort routine by date on list
ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range
("A5:A500") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Master").Sort
.SetRange Range("A5:J500")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Dim c As Range, compdate As Date
compdate = Range("A1").Value




If IsDate(compdate) Then
For Each c In Range("A5:A500", Range("K65536").End(xlUp))
If IsDate(c.Value) And c.Value > compdate Then
c.EntireRow.Delete
End If
Next

End If






If IsDate(compdate) Then
For Each c In Range("A5:A500", Range("K65536").End(xlUp))
If IsDate(c.Value) And c.Value > compdate Then
c.EntireRow.Delete
End If
Next

End If

End Sub