Search code examples
vbaexcelexcel-2010

Clear entire row data except the first row using excel VBA

I am using below code to clear contents from A2:H2 rows except the first one where I have the header. This code works well if there are no blank cells in between. But how can I clear everything eventhough there is a blank cell?

Sub Clear()

Dim s1Sheet As Worksheet

Set s1Sheet = Workbooks("StockScreen.xlsm").Sheets("TimeStampWork")

s1Sheet.Range(s1Sheet.Range("A2:H2"), s1Sheet.Range("A2:H2").End(xlDown)).ClearContents

End Sub
Solution

  • Below is my sample code: 

    Sub ClearContentExceptFirst()
    Rows("2:" & Rows.Count).ClearContents
End Sub