Also, notice I left out the space in the name

Sub setvarnamerng()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
Range("a4:J" & lr).Name = "HvacTable"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mouimet" wrote in message
...
HI,

I'm trying to name a table using vba and
using relative references.
When we create the file from the database, the table change size.
The macro I have right now do many changes in the table
then, name the table to be use later in the macro for a Pivot Table
I save this macro in Personnal Macro

My problem
The macro always name the table using the same size.
How to create a macro using relative reference.
Rules:
The table always start at cell A4
Column A always have data
The table always stop at column J
Sometimes the last cell in column J do not have data.

Here what I tried the last time
I extract only the parts to name the table.


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


Thanks