Clear contents of worksheet before populating

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi everyone, I need a piece of code that will allow me to
clear the contents of an entire worksheet so I can
repopulate it with fresh information.

Any suggestions?

Thanks
 
Tom,

Try

ActiveSheet.Cells.Clear
' or
ActiveSheet.Cells.Clear

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Of course, the first line should be

ActiveSheet.Cells.ClearContents


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Chip, In the VB application I'm accessing four separate
worksheets at different times and writing to two others at
different times. I want to clear one certain worksheet at
the beginning of the code. Is there a way to statically
tell the VB code which worksheet to clear?

Thanks
 
Tom,

Sure you can. Instead of ActiveSheet, just specify the
appropriate sheet. For example,

Worksheets("Sheet2").Cells.ClearContents


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Sheets("Sheet3").Cells.ClearContents

Instead of Activesheet use a sheet name.
The sheet don't have to be active Tom
 
This may be a coincidence but a coworker just asked me
almost the same question. Can I clear all cells in a
worksheet EXCEPT for a specific row such as a header row?

-----Original Message-----
Sheets("Sheet3").Cells.ClearContents

Instead of Activesheet use a sheet name.
The sheet don't have to be active Tom

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Tom" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Tom,

Try something like the following:

With Worksheets("Sheet1")
.Range(.Range("A2"),
..UsedRange.SpecialCells(xlCellTypeLastCell)).ClearContents
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Chip, I typed it in exactly as you have suggested, but
it had a few problems so I took out the comma after the A2
and the first period before UsedRange. Now it has Run Time
error 438, Object does not support this property or method.

Any ideas?

Tom
 
Tom,

Most likely it is a problem with the line wrapping in the post.
Try the following:

With Worksheets("Sheet1")
.Range(.Range("A2"), .UsedRange. _
SpecialCells(xlCellTypeLastCell)).ClearContents
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
With Worksheets("Sheet1")
.Range(.Range("A2"), _
.UsedRange.SpecialCells( _
xlCellTypeLastCell)).ClearContents
End With


The code has word wrapped in the email. The above should work.
 
Chip and Tom, I really appreciate all of your patience
with this but I have yet another problem with the code. I
typed it in EXACTLY as posted and when it runs I get a run
time error of 1004 - Unable to get the Special Cells
property of the Range Class.

Do either of you know what this means? I'm running Excel97
on a Win2K machine if that helps.

Thanks
 
Man do I feel stupid...I'm really sorry for wasting your
time on the last post. I mistyped the SpecialCells...I put
in x1 instead of xl (EX-ONE instead of EX-EL).

Thanks again for all of your help and patience, and yes it
works fine now.

Tom - the idiot.
 
If you are running it from the click event of a commandbutton, change the
takefocusonclick property of the commandbutton to false.
 
Back
Top