Thanks Rick,

This works except that the range on the spreadsheet is different with each
report so it doesn't always end on row 100. I need it to find where it ends,
then do the delete.

Any other thoughts?

"Rick Rothstein" wrote:

Perhaps this code will do what you want...

Dim FirstBlankCell As Range
'....
'....
On Error Resume Next
Set FirstBlankCell = Worksheets("Sheet1").Range("A1:J100"). _
SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not FirstBlankCell Is Nothing Then
Rows(FirstBlankCell(1).Row & ":100").ClearContents
End If

Note 1: Since you cannot Undo the deletions from a macro, you should
first test this code out on a copy of your worksheet and not
on your live data.

Note 2: Change my reference to Worksheets("Sheet1") to reflect the
actual worksheet name you want to apply this code to.

--
Rick (MVP - Excel)


"Danielle" wrote in message
...
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



.