Clear Contents of Excel Sheet Except First Row Using VBA

In this post you will learn how to clear the contents of Excel sheet except the first row using VBA. This is a very common requirement for Excel VBA applications. Because VBA applications often need to clear existing reports and re-generate them. When doing this, the program doesn’t need to delete the header row. Because it is the same for the new reports. Also there may be some other situations where you want to develop a VBA code to delete content from the Excel sheet except the first row. For example, sometimes users may need to clear the existing result sheets except the header using buttons.

So now let’s look at how to develop a code to delete the worksheet contents without header row.

Let’s consider this sample worksheet. Name of the worksheet is “Order Information”.

There are few different ways to accomplish this. I will explain two methods in this post. You can use the first method if you know the last column you have data in. In the above sample sheet we have data upto column E. If you don’t know what the last column is or if the last column changes from time to time then you should use the second method shown at the end.

This is the complete code of the first method.

Sub DeleteContentsExceptHeader()

     Dim WS As Worksheet
     Dim LastRow As Long

     Set WS = Worksheets("Order Information")

     LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     If LastRow > 1 Then
         WS.Range("A2:E" & LastRow).ClearContents
     End If

End Sub

This is the result you will get when run the subroutine.

Here is the explanation for the first subroutine.

First we need to define two variables

Dim WS As Worksheet
Dim LastRow As Long

Next, assign the worksheet to the WS variable.

Set WS = Worksheets("Order Information")

Find the last row of the worksheet

LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Next we need to check whether the last row is greater than 1. If it is greater than 1, then we can clear the contents using the Range.ClearContents method.

If LastRow > 1 Then
     WS.Range("A2:E" & LastRow).ClearContents
End If

It is important to check whether the last row is greater than 1, because otherwise the “WS.Range("A2:E" & LastRow).ClearContents” statement will delete the header row if there is no data after row 1.

Now let’s move to the second method. As mentioned earlier, you can use this method if you don’t know the last column of the data or if the last column changes from time to time. Below is the complete code of the second method.

Sub DeleteContentsExceptHeader_Method2()

     Dim WS As Worksheet
     Dim LastRow As Long

     Set WS = Worksheets("Order Information")

     LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     If LastRow > 1 Then
         WS.Rows("2:" & LastRow).ClearContents
     End If

End Sub

In this method we are deleting entire rows from row number two to the last row. It is important to use If Statement (If LastRow > 1 Then) to avoid deletion of the first row in case there is no data after row 1.