Dan,

Change

If .Cells(xR, "G") < Date Then _
Rows(xR).EntireRow.Delete
Rows(31).EntireRow.Insert

to

If .Cells(xR, "G") < Date Then
Rows(xR).EntireRow.Delete
Rows(31).EntireRow.Insert
end if


HTH,
Bernie
MS Excel MVP


"Dan E." wrote in message
...
I have a workbook which contains a worksheet that has project data, including
project end date. Users update the sheet with current and projected
projects. I have written a macro to remove entries whose end date (in column
"G") are older than the current date.

The macro as written works, however when I run the macro it inserts a blank
row for every project listed in the spreadsheet. How do I get it to insert a
new row only when it deletes a row? And how do I make sure that the new,
inserted row is at the bottom of the formatted data (which ends at row 31)?

Sub DeleteOldRows()
Dim LastRow As Long, xR As Long
With ActiveSheet
LastRow = .Cells(Rows.Count, "G").End(xlUp).Row
For xR = LastRow To 6 Step -1
If .Cells(xR, "G") < Date Then _
Rows(xR).EntireRow.Delete
Rows(31).EntireRow.Insert
Next xR
End With

End Sub

Thanks for your help!