In Excel,VBA. I need help to name a range.

[Guest]

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

 

[Guest]

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

 

[R.VENKATARAMAN]

use something like this

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

 

[Guest]

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