How to keep header and delete all data?

Question

I am trying to keep the first row and remove/delete all data of sheet named "Main". My below code does not remove any data from the sheet named "Main".

Sub clean_sheets()

'-------Clear Main Sheet all data will be removed except Header Row-----
With ActiveWorkbook.Worksheets("Main")
   Rows("2:" & Rows.Count).ClearContents
End With

'----Delete all existing worksheets after "Main" Worksheet
' and save the active workbook for next run------

Dim xWs As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name <> "MacroButtons" And xWs.Name <> "Main" Then
        xWs.Delete
    End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

ActiveWorkbook.Save

End Sub

This above is to remove all data except header row and also to remove any other sheet other than sheet "Main".

All I want is to delete all data from sheet named "Main" except header row.

Main Excel data

It should remove all data except row1 header row and resize the sheet.

Answer 1

Try it.

Sub test1()
Sheets("Sheet1").Rows("2:" & Sheets("Sheet1").Rows.Count).ClearContents
End Sub


Sub test2()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
    If ws.Name <> "Sheet1" Then ws.Delete
Next ws
Application.DisplayAlerts = True
End Sub

and try this

Sub test1()
    Application.DisplayAlerts = FALSE
    Sheets("Sheet1").Rows("2:" & Sheets("Sheet1").Rows.Count).ClearContents
    
    Dim ws          As Worksheet
    
    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then ws.Delete
    Next ws
    Sheet1.Columns.AutoFit
    ActiveWorkbook.Save
    Application.DisplayAlerts = TRUE
    
End Sub