VBA Excel how to assign name in variable range

[Mouimet]

I need to name a group of cells in vba. I need to add a few lines just to
name cells in my macro and refer to that range. The problem is the range (row
and Col) is variable. The macro I wrote do not work and keep the same range.
Here my needs:
1- Table always start at cell A4
2- Row and columns are variable and can be anything
3- I need to add this vba line inside a macro.
Here the line I did to name cells.
If I create a new file with more rows the macro keep the same range R20C11 and
name the cells. Macro do not go to the last cells. Why?
Thanks
==========
Range("A4").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
ActiveWorkbook.Names.Add Name:="HvacTable", RefersToR1C1:= _
"=Sheet1!R4C1:R20C11"

 

[John Bundy]

You need to make the last row variable, try this
Sub main()
Dim lastCell As Integer
lastCell = Sheet1.Cells(Rows.Count, "a").End(xlUp).Row
Sheet1.Range("A4").Select
ActiveWorkbook.Names.Add Name:="HvacTable",
RefersToR1C1:="=Sheet1!R4C1:R" & lastCell & "C1"
End Sub

 

[Don Guillett]

Try this. Your defined name needs to be variable. Look in the help index for
OFFSET

Sub variablerange()
Sheets("sheet1").Select
ActiveWorkbook.Names.Add Name:="HvacTable", RefersTo:= _
"=offset($A$3,1,0,counta($r:$r)-3,11)"

' "=Sheet1!R4C1:R20C11"
End Sub

 

[Mouimet]

I tried both the macros (from John and Don) alone first, and the first macro
named only the first column. The second one do not work at all.
Maybe I didn't give you enough details or didn't explain well. Sorry.
Here
1- I use this in personnal macro because I use it with different files.
2- The macro I use right now insert columns, add formula, create Pivot Table
base on the table I'm trying to give a name. (The same macro do all of this).
Naming the table is part of the macro.

3- Everything work except fine when I try to named the table from cell
A4(always) to (variable columns and Rows)Example Col: J,K or L rows 10, 20,
98, etc.
The macro close without making a mistake, however the table is always a4 to
I20
The cell A4 will always be the first cell of the table. The column I will be
normally I all the time. The row can be anything 5 to 3000.

Why when I ask to go to the last cell the macro always stop to row 20 even
if I have 40 rows. I even tried to use the "record macro using the option
"relative References" and it doesn't change. If I use the CTRL-End (shortcut)
the curseur go to the last cell so it's not a problem with the sheets or the
data.
Can you help me on this. Thanks for the work you did already.

 

[Mouimet]

I tried both the macros (from Don and John) and the first macro
named only the first column. The second one do not work at all sorry Don.
Maybe I didn't give you enough details or didn't explain well. Sorry.
Here
1- I use this in personnal macro because I use it with different files.
2- The macro I use right now insert columns, add formula, create Pivot Table
base on the table I'm trying to give a name. (The same macro do all of this).
Naming the table is part of the macro.

3- Everything work fine except when I try to named the table from cell
A4(always) to (variable columns and Rows)Example Col: J,K or L rows 10, 20,
98, etc.
The macro close without making a mistake, however the table is always a4 to
I20
The cell A4 will always be the first cell of the table. The column I will be
normally I all the time. The row can be anything 5 to 3000.

Why when I ask to go to the last cell the macro always stop to row 20 even
if I have 40 rows. I even tried to use the "record macro using the option
"relative References" and it doesn't change. If I use the CTRL-End (shortcut)
the curseur go to the last cell so it's not a problem with the sheets or the
data.
Can you help me on this. Thanks for the work you did already.