Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to enter zero into 240 ranges?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to enter zero into 240 ranges?
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" skrev i meddelelsen ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to enter zero into 240 ranges?
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 "Per Jessen" wrote: 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" skrev i meddelelsen ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to enter zero into 240 ranges?
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? "Al" wrote: 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 "Per Jessen" wrote: 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" skrev i meddelelsen ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to enter zero into 240 ranges?
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" skrev i meddelelsen ... 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? "Al" wrote: 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 "Per Jessen" wrote: 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" skrev i meddelelsen ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to enter zero into 240 ranges?
Many thanks...I'll try this first thing on Monday when I'm back in the office!
Al "Per Jessen" wrote: 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" skrev i meddelelsen ... 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? "Al" wrote: 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 "Per Jessen" wrote: 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" skrev i meddelelsen ... 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to enter zero into 240 ranges?
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" wrote: 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" skrev i meddelelsen ... 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? "Al" wrote: 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 "Per Jessen" wrote: 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" skrev i meddelelsen ... 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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to enter zero into 240 ranges?
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 "Al" skrev i meddelelsen ... 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" wrote: 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" skrev i meddelelsen ... 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? "Al" wrote: 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 "Per Jessen" wrote: 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" skrev i meddelelsen ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to select cells in column enter data then press enter | New Users to Excel | |||
Macro Ranges | Excel Discussion (Misc queries) | |||
How can I stop Excel from turning the number ranges I enter (eg 1. | New Users to Excel | |||
macro to change date ranges | Excel Discussion (Misc queries) | |||
Relative Ranges in excel macro | Excel Discussion (Misc queries) |