How do I clear all cells from all rows except the first row in Sheet1?

here's one way, but change the "A" to whatever column has the last row of data

Sub test()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("2:" & lastrow).ClearContents
End Sub

--


Gary


"Michael" wrote in message
oups.com...
How do I clear all cells from all rows except the first row in Sheet1?

Michael,
Here's one way. UsedRange allows for situations if you have blank rows above
the header row. If this is never the case you can delete it.

Private Sub CommandButton1_Click()

With Worksheets("Sheet1").UsedRange
.Range("A2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With

End Sub

NickHK

"Michael" wrote in message
oups.com...
How do I clear all cells from all rows except the first row in Sheet1?

As long as the used range doesn't include the last row of the worksheet:

Worksheets("Sheet1").UsedRange.Offset(1).ClearCont ents

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Michael" wrote in message
oups.com...
How do I clear all cells from all rows except the first row in Sheet1?