PDA

View Full Version : [SOLVED] Clear Cells Except First Row



sheeeng
06-23-2005, 03:00 AM
Hi all, :hi:

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

Bob Phillips
06-23-2005, 03:17 AM
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
06-23-2005, 03:20 AM
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
06-23-2005, 03:29 AM
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
06-23-2005, 03:33 AM
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
06-23-2005, 03:50 AM
What is used range?

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

sheeeng
06-23-2005, 04:53 AM
Thanks. Can we display the value of UsedRange?

Bob Phillips
06-23-2005, 07:04 AM
Thanks. Can we display the value of UsedRange?

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

MsgBox Activesheet.UsedRange.Address

sheeeng
06-23-2005, 08:21 AM
Thanks. Another solved.