excelvba

Clear Column Data except first Headers |Excel|VBA|


I want to Delete the Column Data of "A" and "B" on button click => except the First Cell of both the Columns A and B which are headers

My Sheet :

Sheet

    Private Sub CommandButton1_Click()

        Dim myRange As Range
        Set myRange = ThisWorkbook.Worksheets("Sheet2").Range("A:B")
        myRange.Clear

   End Sub

Clear only Data [except the first Headers of A:B]


Solution

  • Here:

    Option Explicit
    Private Sub CommandButton1_Click()
    
        Dim myRange As Range
        Dim LastRow As Long 'declare a long variable to find the last row
    
        With ThisWorkbook.Sheets("Sheet2")
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'last row on column A
            Set myRange = .Range("A2:B" & LastRow) 'this way you avoid headers and clear everything
            myRange.Clear
        End With
    
    End Sub