Clear Cells Except First Row

[sheeeng]

Hi all, :hi:

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

[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. :friends:

[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.