Macro for clearing cell contents

  • Thread starter Thread starter Sal
  • Start date Start date
S

Sal

Hi there,

I have a 52 sheet workbook and I would like a macro to clear the contents of
cells starting at row 2 onwards for all 52 sheets. However, some columns
contain formulas which I would like to preserve.

Any ideas would be gratefully received.

Many thanks
 
Assuming identical structured sheets,
you could try something like this ..

Sub ClearContents()
Dim wkSht As Worksheet
For Each wkSht In Worksheets
wkSht.Range("A2:C3000,F2:G3000").ClearContents
'edit ranges to suit max row & cols to exclude (eg cols D,E in above)
Next wkSht
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
 
Hi Max,

Many thanks, this works really well as I have excluded the columns with
formulas in them. This will save me heaps of time.
 
Hi,

When I copy this code to test it I get a syntax error on the following:

wkSht.Range("A2:C3000,F2:G3000").SpecialCells(xlCellTypeConstants,
xlNumbers + xlTextValues).ClearContents

Any ideas? The suggestion from Max worked for my purposes but it would be
useful to understand your code also.

Many thanks for your time.
 
wkSht.Range("A2:C3000,F2:G3000").SpecialCells(xlCellTypeConstants,
xlNumbers + xlTextValues).ClearContents
Click to expand...

You got hit by line wrap on the line above.

Add a line-continuation mark(_) to have Excel treat it as one line.

wkSht.Range("A2:C3000,F2:G3000").SpecialCells(xlCellTypeConstants, _
xlNumbers + xlTextValues).ClearContents


Gord Dibben MS Excel MVP
 
Glad it helped. Appreciate it you would sign-in as the original poster name
"Sal", then mark that earlier response by pressing the YES button (like the
one below).
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
 
Back
Top