If you are running it from the click event of a commandbutton, change the
takefocusonclick property of the commandbutton to false.

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
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
-----Original Message-----
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

"Tom" wrote in
message
...
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
-----Original Message-----
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


"Tom" wrote in
message
...
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)
www.rondebruin.nl



"Tom" wrote in
message news:a80f01c3b837$62673970
...
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
-----Original Message-----
Tom,

Try

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

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


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


.



.



.



.