select contents of entire worksheet

[Dave]

I'm trying to clear the contents of an entire worksheet
from a macro but I can't find any way of doing this.
I don't want to delete the sheet only clear the contents.

I've gone round in circles with the range(???).clearcontents
style as I can only see that defining the maximum number of
possible cells (ie range("A1:IV65536").clearcontents) will ensure
that the whole sheet is cleared.

Am I just missing something obvious ? or is this the way to do it.

Dave

 

[Chip Pearson]

Dave,

Try something like

ActiveSheet.UsedRange.ClearContents


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Jeff Coleman]

Dave you can use a any of the following:
cells.clear
worksheets("Sheet1").cells.clear
Sheet1.cells.clear
Worksheets(1).cells.clear

Jeff

 

[steve]

Dave,

Its a little sneaky, that's why you missed it... Also you may not be
familiar yet with the Cells object.

Range("$A$1") is the same as Cells(1,1) in code.
Range("$A$1:$E$6") is the same as Range(Cells(1,1),Cells(5,6))

Cells().ClearContents

Cells() usually takes arguments for row and column. Not putting in
arguments specifies all cells.

I like the cells object since it is easier for me to use variables in it
with less confusion and typos.

steve

 

[RogerM]

In VBA this seems to work for me


Dim sht As worksheet

Set sht = Worksheets("Balance Sheet")
With sht.Cells
.ClearContents
.ClearOutline
.Clear
End With

 

[steve bell]

Careful out there...

Cells.Clear clears everything, contents, format, etc.
Cells.ClearContents just clears the contents.

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!