One way:

Public Sub ClearCells1()
Dim nRows As Long
Dim nCols As Long
With Range("B4")
If IsEmpty(.Value) Then Exit Sub
If IsEmpty(.Offset(1, 0).Value) Then
nRows = 1
Else
nRows = Application.Min(18, .End(xlDown).Row) - 3
End If
nCols = .End(xlToRight).Column - 1
.Resize(nRows, nCols).ClearContents
End With
End Sub



In article ,
RichT wrote:

Hi,
Looking for some very basic VB assistance.

I use a range of cells to hold some temporary data, after which I clear
the range with a macro.
The range always begins at "B4"and has a fixed number of columns, but
the number of rows could be between 1 and 15.
I currently do this (messily) as follows:

Sub ClearCells1()
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("B4").Select
End Sub

which I have now discovered doesn't work if there's only 1 row of
data,
and is lethal if there's no data in the range.
Any assistance would be greatly appreciated.