Ossie,

Along those same lines, is there a couple lines of code that will clear the
contents on all of the rows below the last of row of contiguous data, even if
the rows are hidden.

In other words, a set of data is in A1:J100, and then there are about 20
blank rows, then some more data starts in A120. I'd want the code to find the
last row of the initial contiguous data (100), then delete everything in the
spreadsheet below it even if some rows are hidden.

Thanks again!
Dani

"OssieMac" wrote:

Hi Dani,

Here are 2 options. See the comments for when to use the option.

'Option 1
Sub DeleteRows1()
'When a specific column will always have data in last row.

Dim lastRow As Long

With Sheets("Sheet1")
'Can replace "A" with any column that
'will always have data in the last row.
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(lastRow - 1 & ":" & lastRow).Delete

End With

End Sub


'Option 2
Sub DeleteRows2()

'If it is not know which column
'will always have data in the last row.

Dim lastRow As Long

With Sheets("Sheet1")

lastRow = .Cells _
.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row

.Rows(lastRow & ":" & lastRow - 1).Delete

End With

End Sub

--
Regards,

OssieMac