| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Range Names
I need to create a macro that will create a range name - I'm OK to do that
but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
I don't know where the range is, but something akin to this should work
Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message . .. I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
I get an error message when I tried that - I must be missing something -
"object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message . .. I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
Possible typo of Count.
Set rng = Range("C11", Cells(Rows.Count, "C").End(xlUp)) Bob, your fingers must bleed with the number of post you do each day ;) Cheers Andy SamDev wrote: I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message m... I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
Andy, good eyes....
this works but I need the macro to start in C11 and go over to column O - this macro only includes row C - I know I'm just missing something simple so Andy & Bob - thanks, in advance, for your help. "Andy Pope" wrote in message ... Possible typo of Count. Set rng = Range("C11", Cells(Rows.Count, "C").End(xlUp)) Bob, your fingers must bleed with the number of post you do each day ;) Cheers Andy SamDev wrote: I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message om... I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
Couple of typos
Set rng = Range("C2",Cells(Rows.Count,"C").End(xlUp)) rng.Name = "myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message . .. I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message . .. I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
Maybe:
Set rng = Range("C11:O", Cells(Rows.Count, "C").End(xlUp).row) Still using column C to get the lastrow. SamDev wrote: Andy, good eyes.... this works but I need the macro to start in C11 and go over to column O - this macro only includes row C - I know I'm just missing something simple so Andy & Bob - thanks, in advance, for your help. "Andy Pope" wrote in message ... Possible typo of Count. Set rng = Range("C11", Cells(Rows.Count, "C").End(xlUp)) Bob, your fingers must bleed with the number of post you do each day ;) Cheers Andy SamDev wrote: I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message om... I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
Doesn't work - get message "Method of Range Object Failed"
Any other ideas? Much thanks! "Dave Peterson" wrote in message ... Maybe: Set rng = Range("C11:O", Cells(Rows.Count, "C").End(xlUp).row) Still using column C to get the lastrow. SamDev wrote: Andy, good eyes.... this works but I need the macro to start in C11 and go over to column O - this macro only includes row C - I know I'm just missing something simple so Andy & Bob - thanks, in advance, for your help. "Andy Pope" wrote in message ... Possible typo of Count. Set rng = Range("C11", Cells(Rows.Count, "C").End(xlUp)) Bob, your fingers must bleed with the number of post you do each day ;) Cheers Andy SamDev wrote: I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message om... I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
Not half as much as trying to type up some patient assessments for my wife.
Lots of long sentences with long words, and I am very much a finger typist, with fingers bigger than the keys. Bob "Andy Pope" wrote in message ... Possible typo of Count. Set rng = Range("C11", Cells(Rows.Count, "C").End(xlUp)) Bob, your fingers must bleed with the number of post you do each day ;) Cheers Andy SamDev wrote: I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message m... I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
Don't need the Row, try
Set rng = Range("C11:O", Cells(Rows.Count, "C").End(xlUp)) -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message .. . Doesn't work - get message "Method of Range Object Failed" Any other ideas? Much thanks! "Dave Peterson" wrote in message ... Maybe: Set rng = Range("C11:O", Cells(Rows.Count, "C").End(xlUp).row) Still using column C to get the lastrow. SamDev wrote: Andy, good eyes.... this works but I need the macro to start in C11 and go over to column O - this macro only includes row C - I know I'm just missing something simple so Andy & Bob - thanks, in advance, for your help. "Andy Pope" wrote in message ... Possible typo of Count. Set rng = Range("C11", Cells(Rows.Count, "C").End(xlUp)) Bob, your fingers must bleed with the number of post you do each day ;) Cheers Andy SamDev wrote: I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message om... I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
Oops. I had a typo:
Set rng = Range("C11:O" & Cells(Rows.Count, "C").End(xlUp).row) (I forgot to change the comma to an ampersand--sorry.) SamDev wrote: Doesn't work - get message "Method of Range Object Failed" Any other ideas? Much thanks! "Dave Peterson" wrote in message ... Maybe: Set rng = Range("C11:O", Cells(Rows.Count, "C").End(xlUp).row) Still using column C to get the lastrow. SamDev wrote: Andy, good eyes.... this works but I need the macro to start in C11 and go over to column O - this macro only includes row C - I know I'm just missing something simple so Andy & Bob - thanks, in advance, for your help. "Andy Pope" wrote in message ... Possible typo of Count. Set rng = Range("C11", Cells(Rows.Count, "C").End(xlUp)) Bob, your fingers must bleed with the number of post you do each day ;) Cheers Andy SamDev wrote: I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message om... I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Range Names
The ampersand was the "ticket" - Thanks to all who helped - this will make
my life soooo much easier. I can use this in so many spreadsheets. "Dave Peterson" wrote in message ... Oops. I had a typo: Set rng = Range("C11:O" & Cells(Rows.Count, "C").End(xlUp).row) (I forgot to change the comma to an ampersand--sorry.) SamDev wrote: Doesn't work - get message "Method of Range Object Failed" Any other ideas? Much thanks! "Dave Peterson" wrote in message ... Maybe: Set rng = Range("C11:O", Cells(Rows.Count, "C").End(xlUp).row) Still using column C to get the lastrow. SamDev wrote: Andy, good eyes.... this works but I need the macro to start in C11 and go over to column O - this macro only includes row C - I know I'm just missing something simple so Andy & Bob - thanks, in advance, for your help. "Andy Pope" wrote in message ... Possible typo of Count. Set rng = Range("C11", Cells(Rows.Count, "C").End(xlUp)) Bob, your fingers must bleed with the number of post you do each day ;) Cheers Andy SamDev wrote: I get an error message when I tried that - I must be missing something - "object doesn't support this property or method" Set rng = Range("C11", Cells(Rows.FCount, "C").End(xlUp)) rng.Name = data Above is what I set tried - C11 is the starting point and name for the range is "data". Thx "Bob Phillips" wrote in message ... I don't know where the range is, but something akin to this should work Set rng = Range("C2",Cells(Rows.FCount,"C").End(xlUp)) rng.Name = 2myRange" -- HTH RP (remove nothere from the email address if mailing direct) "SamDev" wrote in message om... I need to create a macro that will create a range name - I'm OK to do that but my issues is each month the range name's "range" changes. I have set it up so each month the original range name is deleted and a new one created that covers the additional rows of data. The starting cell (row/column) and the number of columns stays the same, it's just the number of rows that change. I know there are ways to count the number of rows but not sure how it set this up with the range name - any help would be appreciated. Thx. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
| Reference Range Names in Macro | Excel Discussion (Misc queries) | |||
| COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
| Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Linear Mode
