Excel VBA - Clearing contents of a sheet below a Row

Hi Folks,

I'm quite new to Excel VBA and I am having a difficult time figuring out how to clear the contents of a sheet below a specified cell or row.

I'm thinking it might use cells.specialcells and the .clearcontents method but I am not clear on exactly how. Could someone proivde an example where my sheet is sheets("Audit Sheet") and a variable HeaderRow containing a row number such as 16 for which I want everything cleared below regardless of content?

Thanks in advance for the help.



  • Hi Davidr41,
    clearcontents will clear contents but not any coloring or any other possible customizing of cells. Perhaps you should use , always taking into account that excel shifts rows and columns (according to what has been deleted) in order to fill in the gaps.
    If you really want to clear everything EVERYTHING from e.g. row 16 downwards, consider using the following code:

    sub DeleteRows(ByVal HeaderRow as Long)
    'Deletes all Rows starting from HeaderRow
    'to the last row of the Worksheet
    'Set the range to be deleted
    Dim rngRange as Range
    With Sheets("Audit Sheet")
    Set rngRange = .Range _
    (.Cells(HeaderRow, 1), .Cells(.Rows.Count,1)).EntireRow
    End With
    'Delete this range
    End Sub

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion