Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name a variable range
Hi,
I have a sheet which is updated whenever workbook is opened. What I need to do is update named ranges. As you can see in the code below, the code still refers to R number. But when the workbook is updated the last row might R1327, which I need to include. So basically how can I create a code that helps me with dynamic ranges. Note: I tried using reference =Sheet1!$A$1:INDEX(Sheet1!$1:$65536;COUNTA(Sheet1! $A:$A);COUNTA(Sheet1!$1:$1)) which worked fine in the past, but as I use a sumproduct function in the workbook I cannot include cells that are blank (so it seems). Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="dates", RefersToR1C1:= _ "='Source Data'!R2C7:R1314C7" Kind regards, Basta |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name a variable range
'Last filled row in Column A
lngLastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row OR Dim rngData As Range Set rngData = Range("A1", Cells(Rows.Count, "A").End(xlUp)) MsgBox rngData.Address If this post helps click Yes --------------- Jacob Skaria "Basta1980" wrote: Hi, I have a sheet which is updated whenever workbook is opened. What I need to do is update named ranges. As you can see in the code below, the code still refers to R number. But when the workbook is updated the last row might R1327, which I need to include. So basically how can I create a code that helps me with dynamic ranges. Note: I tried using reference =Sheet1!$A$1:INDEX(Sheet1!$1:$65536;COUNTA(Sheet1! $A:$A);COUNTA(Sheet1!$1:$1)) which worked fine in the past, but as I use a sumproduct function in the workbook I cannot include cells that are blank (so it seems). Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="dates", RefersToR1C1:= _ "='Source Data'!R2C7:R1314C7" Kind regards, Basta |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name a variable range
rather tahn VBA, just make the range dynamic?
in Insert/Name/Define select dates then set the refers to: =OFFSET(Sheet1!IR1,0,0,COUNTA(Sheet1!IR:IR),2) once done the range will grow/shrink depending on the number of items in column A....BUT beware, there must be no gaps "Basta1980" wrote: Hi, I have a sheet which is updated whenever workbook is opened. What I need to do is update named ranges. As you can see in the code below, the code still refers to R number. But when the workbook is updated the last row might R1327, which I need to include. So basically how can I create a code that helps me with dynamic ranges. Note: I tried using reference =Sheet1!$A$1:INDEX(Sheet1!$1:$65536;COUNTA(Sheet1! $A:$A);COUNTA(Sheet1!$1:$1)) which worked fine in the past, but as I use a sumproduct function in the workbook I cannot include cells that are blank (so it seems). Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="dates", RefersToR1C1:= _ "='Source Data'!R2C7:R1314C7" Kind regards, Basta |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name a variable range
Hi Patrick,
I'm not only one working with workbook. Therefore I want to automate it. Regards, Basta "Patrick Molloy" wrote: rather tahn VBA, just make the range dynamic? in Insert/Name/Define select dates then set the refers to: =OFFSET(Sheet1!IR1,0,0,COUNTA(Sheet1!IR:IR),2) once done the range will grow/shrink depending on the number of items in column A....BUT beware, there must be no gaps "Basta1980" wrote: Hi, I have a sheet which is updated whenever workbook is opened. What I need to do is update named ranges. As you can see in the code below, the code still refers to R number. But when the workbook is updated the last row might R1327, which I need to include. So basically how can I create a code that helps me with dynamic ranges. Note: I tried using reference =Sheet1!$A$1:INDEX(Sheet1!$1:$65536;COUNTA(Sheet1! $A:$A);COUNTA(Sheet1!$1:$1)) which worked fine in the past, but as I use a sumproduct function in the workbook I cannot include cells that are blank (so it seems). Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="dates", RefersToR1C1:= _ "='Source Data'!R2C7:R1314C7" Kind regards, Basta |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name a variable range
Hi Patrick,
I'm not only one working with workbook. Therefore I want to automate it. Regards, Basta "Patrick Molloy" wrote: rather tahn VBA, just make the range dynamic? in Insert/Name/Define select dates then set the refers to: =OFFSET(Sheet1!IR1,0,0,COUNTA(Sheet1!IR:IR),2) once done the range will grow/shrink depending on the number of items in column A....BUT beware, there must be no gaps "Basta1980" wrote: Hi, I have a sheet which is updated whenever workbook is opened. What I need to do is update named ranges. As you can see in the code below, the code still refers to R number. But when the workbook is updated the last row might R1327, which I need to include. So basically how can I create a code that helps me with dynamic ranges. Note: I tried using reference =Sheet1!$A$1:INDEX(Sheet1!$1:$65536;COUNTA(Sheet1! $A:$A);COUNTA(Sheet1!$1:$1)) which worked fine in the past, but as I use a sumproduct function in the workbook I cannot include cells that are blank (so it seems). Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="dates", RefersToR1C1:= _ "='Source Data'!R2C7:R1314C7" Kind regards, Basta |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range to VLOOKUP as a Variable (range in another file) | Excel Programming | |||
select range and put range address in variable | Excel Programming | |||
Macro to copy a specified range to a variable range | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |