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
---
"Sal" wrote:
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

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


Max;175030 Wrote:
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
'Free file hosting by Savefile.com'
(http://savefile.com/projects/236895)
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Sal" wrote:
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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48382

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.

"Max" wrote:

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
---
"Sal" wrote:
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

Hi,

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

wkSht.Range("A2:C3000,F2:G3000").SpecialCells(xlCe llTypeConstants,
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.

"JBeaucaire" wrote:


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


Max;175030 Wrote:
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
'Free file hosting by Savefile.com'
(http://savefile.com/projects/236895)
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Sal" wrote:
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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48382

wkSht.Range("A2:C3000,F2:G3000").SpecialCells(xlC ellTypeConstants,
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(xlCe llTypeConstants, _
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
---
"Mustang" wrote:
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.