Deleting Many Cells on a Sheet

J

Jim

I am using Excel 2002 and have a sheet which Ranges from E5:O78.
There are 20 ranges like E5:F18, H5:F18 etc which has only input info as in
a time sheet.
I need to delete the info with a macro but need to keep the Formulas outside
these Ranges
intact. I have been successfully using:
ActiveSheet.Unprotect
Range("E5:F18,H5:I18,K5:L18,etc....................).ClearContents
ActiveSheet.Protect
but the formula length become unwieldy with 20 Ranges to clear, is there one
Formula which
will only delete non formula cells?
Many thanks
Jim
 
R

Ron de Bruin

Select the range E5:L18 for example
Press F5
click on special
choose constants
press the delete key

Or with VBA

Range("E5:L18").SpecialCells(xlCellTypeConstants).ClearContents
 
J

Jim

Many thanks Ron, much easier!!


Ron de Bruin said:
Select the range E5:L18 for example
Press F5
click on special
choose constants
press the delete key

Or with VBA

Range("E5:L18").SpecialCells(xlCellTypeConstants).ClearContents
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Assigning Formula to a Range 5
Copy sheet and rename tab 4
Change event on single cell fires many times 3
Help ! How do I do this in VB Script 3
Patse Rows from one Sheet to another with a Twist 18
data from 1 sheet to the next 6
Working in Excel 2003 but not Excel 2007. Can not protect sheet. 2
Search other sheets for numbers & color them 10

Top