Excel VBA - Clearing contents of a sheet below a Row - Programmers Heaven

Howdy, Stranger!

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


Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Excel VBA - Clearing contents of a sheet below a Row

davidr41davidr41 Posts: 1Member
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.



  • tstavtstav Posts: 4Member
    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.