CODEWITHSUNDEEP
vbaexcelexcel-2010
acr
acr

Reputation: 1746

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

Upvotes: 9

Views: 67987

Answers (5)

Pankaj
Pankaj

Reputation: 1

I use below code which checks whether there is any row to delete.

If Application.ActiveSheet.UsedRange.Rows.Count > 1 Then
    
    ActiveSheet.Rows("2:" & Application.ActiveSheet.UsedRange.Rows.Count).ClearContents
    
End If

Upvotes: 0

Hari Das
Hari Das

Reputation: 10864

This is what works for me

Sheets("MY_SHEET_NAME").Rows("2:" & Sheets("MY_SHEET_NAME").Rows.Count).ClearContents

Upvotes: 3

bluetata
bluetata

Reputation: 654

Below is my sample code: 

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

Upvotes: 20

Jurjen
Jurjen

Reputation: 1396

If you simply want to delete the entire row from a specific row number to a specific row number, you can avoid using the Range and Cell properties, but instead you can use the Rows property:

s1Sheett.Rows("2:10").ClearContents

Or if you want to delete the everything from a specific row number to the last row with data, you can use:

s1Sheett.Rows("2:" & currentSheet.Rows.Count).ClearContents

Upvotes: 6

Mrig
Mrig

Reputation: 11702

Instead of 

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

try

s1Sheet.Range("A2:H" & s1Sheet.Cells(s1Sheet.Rows.Count, "A").End(xlUp).Row).

or

With s1Sheett
    .Range("A2:H" & .Cells(.Rows.Count, "A").End(xlUp).Row).ClearContents
End With

here last row with data is counted using Column A.

Upvotes: -1

Related Questions