Name a range with relative reference

[Mouimet]

How to create name using relative reference? The table will be use in a Pivot
Table later in the macro. This is why I need to name a table.
The table, change size everytime we extract this from a database.
The macro is in the Personnal Macro to be use with many files.
Right now the name is create, however I am missing some data when the table
change size. The macro doesn't go under row 55
I tried different ways and nothing work.
Rules:
The table always start at cell: A4
Column A always have data and I use this one to trigger the last row of the
table.
# Of Rows change all the time.
The last column is always: J
Sometimes the last cells in column J do not have data
Here what I did the last time

Range("A4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="HvacTable", RefersToR1C1:= _
"=Sheet1!R4C1:R55C12"
ActiveCell.Select

Thank you very much

 

[Joel]

Range("A4").Select
Set MyRange = Range(Selection, ActiveCell.SpecialCells(xlLastCell))
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="HvacTable", RefersToR1C1:= _
"=" & MyRange.Address

 

[Dave Peterson]

Dim myRng as range
Dim LastRow as range

with worksheets("somesheetnamehere")
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("A4:J" & lastrow)
myrng.name = "HvacTable"
end with

You may want to drop the name stuff from your code and use a dynamic range name.

Assuming that there is always something in A1:A3 (even a formula that makes the
cell look empty, like: =""), you could use:

=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-3,10)

(change the sheet name as required (twice).)

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic