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

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

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

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

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