Clearing worksheets also clears the formulas. So does delete. How can I
clear a worksheets without destroying the formulas in the worksheet.

I run macro, edit it then want to run again but with the worksheets
cleared. When I clear them, I have to reenter all the formulas.

JOhn

Hi John,

Try:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , _
xlNumbers).ClearContents

---
Regards,
Norman



"John" wrote in message
...
Clearing worksheets also clears the formulas. So does delete. How can I
clear a worksheets without destroying the formulas in the worksheet.

I run macro, edit it then want to run again but with the worksheets
cleared. When I clear them, I have to reenter all the formulas.

JOhn

That works great, thanks. Now, could I change it so that it worked only
on area selected?

Thanks
John

Norman Jones wrote:

Hi John,

Try:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , _
xlNumbers).ClearContents

---
Regards,
Norman



"John" wrote in message
...

Clearing worksheets also clears the formulas. So does delete. How can I
clear a worksheets without destroying the formulas in the worksheet.

I run macro, edit it then want to run again but with the worksheets
cleared. When I clear them, I have to reenter all the formulas.

JOhn





--
R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s
Change LID to

Hi John,

Try:


Selection.SpecialCells(xlCellTypeConstants, _
xlNumbers).ClearContent


---
Regards,
Norman



"John" wrote in message
...
That works great, thanks. Now, could I change it so that it worked only on
area selected?

Thanks
John

Norman Jones wrote:

Hi John,

Try:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , _
xlNumbers).ClearContents

---
Regards,
Norman



"John" wrote in message
...

Clearing worksheets also clears the formulas. So does delete. How can I
clear a worksheets without destroying the formulas in the worksheet.

I run macro, edit it then want to run again but with the worksheets
cleared. When I clear them, I have to reenter all the formulas.

JOhn





--
R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s
Change LID to

Selection.SpecialCells(xlCellTypeConstants, _
xlNumbers).ClearContents

--
Regards,
Tom Ogilvy

"John" wrote in message
...
That works great, thanks. Now, could I change it so that it worked only
on area selected?

Thanks
John

Norman Jones wrote:

Hi John,

Try:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , _
xlNumbers).ClearContents

---
Regards,
Norman



"John" wrote in message
...

Clearing worksheets also clears the formulas. So does delete. How can I
clear a worksheets without destroying the formulas in the worksheet.

I run macro, edit it then want to run again but with the worksheets
cleared. When I clear them, I have to reenter all the formulas.

JOhn





--
R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s
Change LID to

Changed to Selection.Cells.SpecialCells(xlCellTypeConstants, _
xlNumbers).ClearContents

And it works perfectly though I have no idea HOW it works.

Thanks

John

Norman Jones wrote:
Hi John,

Try:


Selection.SpecialCells(xlCellTypeConstants, _
xlNumbers).ClearContent


---
Regards,
Norman



"John" wrote in message
...

That works great, thanks. Now, could I change it so that it worked only on
area selected?

Thanks
John

Norman Jones wrote:


Hi John,

Try:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , _
xlNumbers).ClearContents

---
Regards,
Norman



"John" wrote in message
. ..


Clearing worksheets also clears the formulas. So does delete. How can I
clear a worksheets without destroying the formulas in the worksheet.

I run macro, edit it then want to run again but with the worksheets
cleared. When I clear them, I have to reenter all the formulas.

JOhn




--
R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s
Change LID to





--
R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s
Change LID to

Hi John,

The code is the programmatic equivalent of the manual Steps:

Edit | Go To | Special | check Constants | uncheck all but the Numbers
sub-option | OK | hit the delete key

For further information see VBA help on the SpecialCells method.

I'm pleased you picked up the missing "s" !

---
Regards,
Norman



"John" wrote in message
...
Changed to Selection.Cells.SpecialCells(xlCellTypeConstants, _
xlNumbers).ClearContents

And it works perfectly though I have no idea HOW it works.

Thanks

John

Norman Jones wrote:
Hi John,

Try:


Selection.SpecialCells(xlCellTypeConstants, _
xlNumbers).ClearContent


---
Regards,
Norman



"John" wrote in message
...

That works great, thanks. Now, could I change it so that it worked only
on area selected?

Thanks
John

Norman Jones wrote:


Hi John,

Try:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , _
xlNumbers).ClearContents

---
Regards,
Norman



"John" wrote in message
.. .


Clearing worksheets also clears the formulas. So does delete. How can I
clear a worksheets without destroying the formulas in the worksheet.

I run macro, edit it then want to run again but with the worksheets
cleared. When I clear them, I have to reenter all the formulas.

JOhn




--
R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s
Change LID to





--
R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s
Change LID to