VBA Loop through All Sheets Except Sheet1

[Stephen_IV]

I need a way to loop throught all sheets except for Sheet1 and delete all of the used range except the header row. There are 6 worksheets. Can someone please help me.

Thanks in advance.
Stephen

 

[Domenic]

Try...

Option Explicit

Sub test()

    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            With ws.UsedRange
                .Resize(.Rows.Count-1).Offset(1, 0).ClearContents
            End With
        End If
    Next ws
    
End Sub

 

[Zack Barresse]

With only 6 sheets, why not just do each one? Do you really need a loop??

Sheet1.Range("2:" & Sheet1.Rows.Count).Clear
Sheet2.Range("2:" & Sheet2.Rows.Count).Clear
Sheet3.Range("2:" & Sheet3.Rows.Count).Clear
Sheet4.Range("2:" & Sheet4.Rows.Count).Clear
Sheet5.Range("2:" & Sheet5.Rows.Count).Clear
Sheet6.Range("2:" & Sheet6.Rows.Count).Clear

 

[SamTYler]

Heh! Or combine your two methods

Option Explicit
Sub test()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Rows("2:" & ws.Rows.Count).Clear
        End If
    Next ws
End Sub

Love the diversity of VBA

 

[Stephen_IV]

Thanks everyone it worked perfect!