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

Dave,

Try something like

ActiveSheet.UsedRange.ClearContents


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Dave" wrote in message
...
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

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

Jeff

-----Original Message-----
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

.

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

"Dave" wrote in message
...
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

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

"Dave" wrote in message
...
Thanks to everyone, its sorted !!

On Thu, 31 Jul 2003 15:36:54 +0100, Dave <newsgroups@edward-
thompson*nospam*.com wrote:

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

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!