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.

'With x' provides a shortcut qualifier. This shortcut qualifier is a leading '.'. So if you need the Rows statement to refer to the rows in ActiveWorkbook.Worksheets("Main") you must precede it with the leadsing '.' to give .Rows...... — freeflow, Feb 11 '21 at 15:44

score 1 · Accepted Answer · answered Feb 11 '21 at 16:59

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

How to keep header and delete all data?

1 Answers1