Clear Range

[scott]

Below is a sample of my worksheet. I'd like to clear the range B1:C3, which
should be the active range minus the Row 1 headers. How can I delete or
clear just the values and not the first row's headers?

LISTING 1:

HeaderA HeaderB HeaderC
20
5 150 75
50


LISTING 2:

HeaderA HeaderB HeaderC

 

[Gary''s Student]

Range("B2:C3").Clear

Note that using Range("B1:C3") would clear that top row as well.

 

[scott]

The problem is that the active range will always be changing, so I need the
target range to basically be the active range minus row 1.

 

[carlo]

If your data starts in A2 you could do following:

Sub delete_rows()

Dim range_str As String
range_str = UsedRange.Address

If UsedRange.Rows.Count > 1 Then
Range("$A$2:" & Right(range_str, _
Len(range_str) - InStr(1, _
range_str, ":"))).Delete
End If

End Sub

hth

Carlo