Clear Cells Except First Row

[sheeeng]

Hi all,

How do I clear all cells in activesheet except first row by macro?
Thanks.

[Bob Phillips]

How do I clear all cells in activesheet except first row by macro?

Dim rng
    Set rng = ActiveSheet.UsedRange
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
    rng.ClearContents

[sheeeng]

Can you explain more bout below code? I don't understand offset. Thanks.

rng.Offset(1, 0)

If I want to dim range as something, is it should be dim rng as range?

Dim rng

What is

UsedRange

Thanks

[Bob Phillips]

Can you explain more bout below code? I don't understand offset. Thanks.

rng.Offset(1, 0)

It just takes the range offset by 1 row, that its bypasses row 1.

If I want to dim range as something, is it should be dim rng as range?

Dim rng

absolutely

Dim rng As Range

[sheeeng]

I had error here. This is because I put in userform terminate.
So even the sheet is empty except 1st row, it runs. How to have alternative to check whether need to clear sheet?

Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)

In Debug, rng.Rows.Count = 0


Solution here.

If (rng.Rows.Count > 1) Then
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
End If

What is used range?

Thanks.

[Bob Phillips]

What is used range?

The range of cells that have been used, a subset of the cells within the worksheet.

[sheeeng]

Thanks. Can we display the value of UsedRange?

[Bob Phillips]

Thanks. Can we display the value of UsedRange?

No, it is an object. You can display its address

MsgBox Activesheet.UsedRange.Address

[sheeeng]

Thanks. Another solved.