In Excel. Using VBA to write code. I need help to name a range where the
upper left is known and the lower right varies

Hi
Say in sheet Sheet1 i have two rows of headers then data starting in A3 (A1
and A2 beeing filled with headers)
The following code creates a workbook-level named range called MyName:

ActiveWorkbook.Names.Add Name:="MyName", RefersTo:= _
"=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-2,1)"

-The range starts in sheet Sheet1!A4
- it is offset by 0 (zero) rows, 0 (zero) columns
- and its size is:
- Counta($A:$A)-2 rows = data rows = number of non empty rows in A:A
minus the 2 non-empty header rows.
- and 1 column
(you could change this part to get a wider range, eg:
CountA(Sheet1!$3:$3) )

I hope this helps
Sebastien

"rangerpooch" wrote:

In Excel. Using VBA to write code. I need help to name a range where the
upper left is known and the lower right varies

use something like this

Range("a1").End(xlDown).End(xlToRight)


rangerpooch wrote in message
...
In Excel. Using VBA to write code. I need help to name a range where the
upper left is known and the lower right varies

by the way, in my previous post
1. the code started the range in A4 and not A3 as i said:
"=OFFSET(Sheet1!$A$4...
to start it in A3, use
"=OFFSET(Sheet1!$A$3...

2.i built a DYNAMIC named-range, ie when you add a row of data, the range
referenced by the named range is automatically adjusted. Eg
Say the named range references A3:A100
Add data in A101
The range now references A3:A101
This due to the formula-type definition.

Maybe you just needed a fixed named range:
To create a fixed named range, assuming you don't know where data stop in
column A, use something like:
Dim Addr as string
Addr = "=Sheet1!$A$3:" & ActiveWorkbook.Worksheets("Sheet1").Range( _
"A65536" ).End(xlUp).Address(True,True,xlA1)
ActiveWorkbook.Names.Add Name:="MyName", RefersTo:= Addr

In this last case, assuming you have data in A3:A100, the code sets the
named range to A3:A100
Now if you add a row in A101, the named range is NOT automatically adjusted.
You need to re-run the code to reajust it.
That is the difference between dynamic and fixed named range.

Regards,
Sebastien

"sebastienm" wrote:

Hi
Say in sheet Sheet1 i have two rows of headers then data starting in A3 (A1
and A2 beeing filled with headers)
The following code creates a workbook-level named range called MyName:

ActiveWorkbook.Names.Add Name:="MyName", RefersTo:= _
"=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-2,1)"

-The range starts in sheet Sheet1!A4
- it is offset by 0 (zero) rows, 0 (zero) columns
- and its size is:
- Counta($A:$A)-2 rows = data rows = number of non empty rows in A:A
minus the 2 non-empty header rows.
- and 1 column
(you could change this part to get a wider range, eg:
CountA(Sheet1!$3:$3) )

I hope this helps
Sebastien

"rangerpooch" wrote:

In Excel. Using VBA to write code. I need help to name a range where the
upper left is known and the lower right varies