macro to enter zero into 240 ranges?

[Al]

I wish to enter the value zero into various ranges within my spreadsheet.
(Wiping clean all prior data entries across the spreadsheet, so that the next
project can start.)
e.g. A7:L21, A25:L37, A42:L57, P7:AB21, P25:AB37, etc etc
There are currently 240 such ranges.
Do these need to be named?
Presumably some VBA code is the answer...can someone suggest something
please?

 

[Per Jessen]

Hi

I think you shold save your spreadsheet (with no date) as a template. Then
you can always start a new project based on the template.

IF you want to clear ranges in current sheet, the macro below will do the
job. Just notice I do'n think you can have all 240 ranges in one statement.

Sub ClearRanges()
Dim ClearRange As Range
Set ClearRange = Application.Union(Range("A7:L21"), Range("A25:L37"))
ClearRange.ClearContents
End Sub

Regards,
Per

 

[Al]

Per, thank you. Noted re the template...we have that currently but human
nature being what it is people always want short-cuts and tend to use a
"completed" spreadheet from an earlier job, rather than a template! Also my
spreadsheet has other data specific to various clients that is constant,
hence the use of an older spreadsheets is actually a good way of doing it.

I'll try your macro...any idea how many ranges I can have?

Al

 

[Al]

Per, I have successfuly used your macro! Thanks, but one thing I have just
noticed...it clears the cells, whereas I would like to enter the value zero
into the cells. (I have custom formatting for "zero" cells, and other If
formulae if the cells are zero values.
How can we change your initital macro?

 

[Per Jessen]

HI Al

Thanks for your reply.

To fill the range with zeros use this line.

ClearRange.Value = 0

Regarding your earlier question, I do'n know how many ranges you can have.
If you reach the limit, use a new variable eg. ClearRange1 and repeat the
statements with the new variable.

Best regards,
Per

 

[Al]

Many thanks...I'll try this first thing on Monday when I'm back in the office!

Al

 

[Al]

Per, it is not Monday but....this worked a treat. Thank you! I have inserted
your recommended row, and my macro now reads.....

Sub ZeroRanges()
Dim ClearRange As Range
Set ClearRange = Application.Union(Range("J13:GJ48"), Range("J55:GJ74"),
Range("J81:GJ100"))
ClearRange.Value = 0
End Sub

As I am not clearing the cells, but entering 0 into all, I was not sure
about the use of the text "ClearRange" I have taken a stab as per the
following:
Sub ZeroRanges()
Dim ZeroRange As Range
Set ZeroRange = Application.Union(Range("J13:GJ48"), Range("J55:GJ74"),
Range("J81:GJ100)
ZeroRange.Value = 0
End Sub

This seems to work too! Have I made any mistakes, or does this seem OK?

 

[Per Jessen]

Hi Al

Thanks for your reply.

Your macro looks very fine. "ClearRange" is just a variable name, ie. you
can change it as you have already seen.

It's good practice to use descriptive variable names.

Best regards,
Per