Macro to Name a Range that isn't pre-defined

[Phoenix333]

Hi Smart Peeplz!

I'm having problems with a bit of code I'm trying to use and I'm hoping someone out there can help.

I need the macro to create a named range based on a last column and row that can change. It won't change once the report is set up, but since this is a template that will be used for hundreds of projects, it needs to be flexible to accomodate a varying numbers of columns or rows

The starting cell will always be A10, but the ending cell can change depending on how many columns and rows are in the table.

Right now, the code I've created is

Sub NamePivotData()

Dim LCol As Long
Dim LRow As Long
Dim RngEnd As Long

LCol = Cells(10, Columns.Count).End(xlToLeft).Column
LRow = Cells(Rows.Count, 1).End(xlUp).Row
RngEnd = Cells(LRow, LCol)

ActiveWorkbook.Names.Add Name:="PivotData", _
RefersTo:="=$A$10:RngEnd"
End Sub

Sadly (and not unexpectedly), I end up with PivotData as a named range that refers to
='Calendar Setup'!$A$10:RngEnd

I need it to refer to ='Calendar Setup'!$A$10:$*$**
where * is the last column in the table and ** is the last row in the table.

As always, your help is GREATLY appreciated!
T-

 

[Phoenix333]

Got it!

I'm posting the answer in case someone else has this same issue.

Sub NamePivotData()
    
    Range("A10").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Names.Add Name:="PivotData", RefersTo:=Selection
End Sub

Thanks!
T-

 

[shg]

Good job.

You can also do it without selecting:

Sub NamePivotData()
    ActiveWorkbook.Names.Add Name:="PivotData", _
                             RefersTo:=Range("A10", Range("A10").End(xlDown).End(xlToRight))
End Sub

 

[Phoenix333]

oooh! I like that better; much cleaner!

Thank you!

 

[shg]

You're welcome, good luck.