Macro for clearing cell contents

[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

 

[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

 

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

 

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

 

[Gord Dibben]

wkSht.Range("A2:C3000,F2:G3000").SpecialCells(xlCellTypeConstants,

xlNumbers + xlTextValues).ClearContents

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

 

[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