select contents of entire worksheet

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
Dave,

Try something like

ActiveSheet.UsedRange.ClearContents


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Dave you can use a any of the following:
cells.clear
worksheets("Sheet1").cells.clear
Sheet1.cells.clear
Worksheets(1).cells.clear

Jeff
 
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
 
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
 
Careful out there...

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

steve
 
Back
Top