Enter value zero into all cells in multiple ranges?
 

I wish to automatically enter a zero across multiple non-adjacent cells so as
to "refresh" a template at the beginning of a calculation. I have 255 such
ranges, although these cell are not yet set up as ranges. (eg j17:t52,
w17:z52, j59:t78, w59:z78......)
I currently keep an empty row - no data, only zeros - and each time I
refresh the spreadsheet I simply copy and paste the empty row across the rows
containing data. I works, but I think there could be an easier way?

Is there such a thing as "set range1, range 2.....range 255 = 0"?

Enter value zero into all cells in multiple ranges?
 

Fhi
probably the simplest way would be this
Sub zapitzero()
Range("A2,A2,G2,A4,E4,f4,A6:A8").Value = 0
End Sub

Add/remove cell references to suit.
you could attach the macro to a custom icon or drop a command button on the
sheet.
Regards
FSt1

"Al" wrote:

I wish to automatically enter a zero across multiple non-adjacent cells so as
to "refresh" a template at the beginning of a calculation. I have 255 such
ranges, although these cell are not yet set up as ranges. (eg j17:t52,
w17:z52, j59:t78, w59:z78......)
I currently keep an empty row - no data, only zeros - and each time I
refresh the spreadsheet I simply copy and paste the empty row across the rows
containing data. I works, but I think there could be an easier way?

Is there such a thing as "set range1, range 2.....range 255 = 0"?

Enter value zero into all cells in multiple ranges?
 

Wow, that was quick! I'm not too familiar with macros, but I'm sure we can
work it out!
How do I drop a command button to the spreadsheet?

"FSt1" wrote:

Fhi
probably the simplest way would be this
Sub zapitzero()
Range("A2,A2,G2,A4,E4,f4,A6:A8").Value = 0
End Sub

Add/remove cell references to suit.
you could attach the macro to a custom icon or drop a command button on the
sheet.
Regards
FSt1

"Al" wrote:

I wish to automatically enter a zero across multiple non-adjacent cells so as
to "refresh" a template at the beginning of a calculation. I have 255 such
ranges, although these cell are not yet set up as ranges. (eg j17:t52,
w17:z52, j59:t78, w59:z78......)
I currently keep an empty row - no data, only zeros - and each time I
refresh the spreadsheet I simply copy and paste the empty row across the rows
containing data. I works, but I think there could be an easier way?

Is there such a thing as "set range1, range 2.....range 255 = 0"?

Enter value zero into all cells in multiple ranges?
 

hi
what version xl do you have?
2003
http://www.mrexcel.com/tip068.shtml
2007
http://office.microsoft.com/en-us/ex...366761033.aspx

it's pretty basic. if you don't like the command button, you could use a
label. they are some times more versital than button in that you can fit them
over a cell easier. the proceedure for labels and command button are about
the same.
the macro would go into a standard module
and you might want to stroll through here too.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

regards
FSt1

"Al" wrote:

Wow, that was quick! I'm not too familiar with macros, but I'm sure we can
work it out!
How do I drop a command button to the spreadsheet?

"FSt1" wrote:

Fhi
probably the simplest way would be this
Sub zapitzero()
Range("A2,A2,G2,A4,E4,f4,A6:A8").Value = 0
End Sub

Add/remove cell references to suit.
you could attach the macro to a custom icon or drop a command button on the
sheet.
Regards
FSt1

"Al" wrote:

I wish to automatically enter a zero across multiple non-adjacent cells so as
to "refresh" a template at the beginning of a calculation. I have 255 such
ranges, although these cell are not yet set up as ranges. (eg j17:t52,
w17:z52, j59:t78, w59:z78......)
I currently keep an empty row - no data, only zeros - and each time I
refresh the spreadsheet I simply copy and paste the empty row across the rows
containing data. I works, but I think there could be an easier way?

Is there such a thing as "set range1, range 2.....range 255 = 0"?

Enter value zero into all cells in multiple ranges?
 

Am using Office Professional 2003 on MS XP.

"FSt1" wrote:

hi
what version xl do you have?
2003
http://www.mrexcel.com/tip068.shtml
2007
http://office.microsoft.com/en-us/ex...366761033.aspx

it's pretty basic. if you don't like the command button, you could use a
label. they are some times more versital than button in that you can fit them
over a cell easier. the proceedure for labels and command button are about
the same.
the macro would go into a standard module
and you might want to stroll through here too.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

regards
FSt1

"Al" wrote:

Wow, that was quick! I'm not too familiar with macros, but I'm sure we can
work it out!
How do I drop a command button to the spreadsheet?

"FSt1" wrote:

Fhi
probably the simplest way would be this
Sub zapitzero()
Range("A2,A2,G2,A4,E4,f4,A6:A8").Value = 0
End Sub

Add/remove cell references to suit.
you could attach the macro to a custom icon or drop a command button on the
sheet.
Regards
FSt1

"Al" wrote:

I wish to automatically enter a zero across multiple non-adjacent cells so as
to "refresh" a template at the beginning of a calculation. I have 255 such
ranges, although these cell are not yet set up as ranges. (eg j17:t52,
w17:z52, j59:t78, w59:z78......)
I currently keep an empty row - no data, only zeros - and each time I
refresh the spreadsheet I simply copy and paste the empty row across the rows
containing data. I works, but I think there could be an easier way?

Is there such a thing as "set range1, range 2.....range 255 = 0"?