How to Clear all Data on sheet after a given row

[Guest]

I am looking for a way to Clear all cells on a speadsheet after a given row
.. For instance: clear all data below row 1000. In particular, I am lookinfg
for a way to do it with out programatically determinig the LAST row that has
data and having to cycle thru each row and delete it. Is there a line of
code that would basically say: Range. Rows( 1001) to Rows("End of spread
sheet data" ).Clear. I dont want to force it to create the sheets row limit
(row 16000+) either, I just want it to know where the last row data is in.
Any assistance would be helpful. Thanks to All

 

[STEVE BELL]

The easiest way is:

Dim lrw As Long

lrw = Cells.SpecialCells(xlLastCell).Row

If lrw > 1000 then
Range(Rows(1000), Rows(lrw)).EntireRow.Delete
End If

Or:

If Cells.SpecialCells(xlLastCell).Row > 1000
Range(Rows(1000),
Rows(Cells.SpecialCells(xlLastCell).Row)).EntireRow.Delete
End If

 

[Dave Peterson]

dim myRow as long
myrow = 1000
range(myrow + 1 & ":65536").clear '.clearcontents


I think I'd use this:

Dim myRow As Long
myRow = 1000
With ActiveSheet
Range(myRow + 1 & ":" & .Rows.Count).Clear '.clearcontents
End With

Then I don't have to worry about when that number of rows gets bigger than 65k!
<vbg>.