Excel VBA - Clearing contents of a sheet below a Row

davidr41

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.

David

tstav

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:

[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
rngRange.Delete
End Sub
[/code]