Macro for clearing cell contents

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
 
M

Max

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
 
J

JBeaucaire

The OP indicated wanting to keep the formulas intact, so this adjustmen
would clear only numeric and text cells
=========
Sub ClearContents(
Dim wkSht As Workshee
For Each wkSht In Worksheet
wkSht.Range("A2:C3000,F2:G3000").SpecialCells(xlCellTypeConstants
xlNumbers + xlTextValues).ClearContents
'edit ranges to suit max row & cols to exclude (eg cols D,E in above
Next wkSh
End Su
=========
 
M

Mustang

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.
 
M

Mustang

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.
 
G

Gord Dibben

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
 
M

Max

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
 
Top