Name a Table relative references

[Mouimet]

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

 

[Don Guillett]

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

 

[Dave Peterson]

Check your other post, too.

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

 

[Mouimet]

Thank you very much.
I tried different things and nothing was working with my files.
You just write the good one for me.
Thanks to you and all others.
I am not sure however I think the others formulas didn't work because in
cell A1,A2 was not empty.
For sure I will use your formula for others files.
Thanks again

 

[Don Guillett]

Glad to help