Help needed naming Ranges with VBA

[zombiemaster]

Hi, all…


I recently inherited a daily report - the data each day has varying number of rows, but always the same number of columns.


The instructions I received include running a macro to set up a number of pivot tables, but first we have to manually name two ranges (the first range is the entire data, the second range is only the data in column “J”).


I thought I could include the naming of the ranges in the VBA to avoid the manual aspect, but am having trouble. I was trying to use the endRow$ feature but I’m getting an error…can someone pick this apart and see what I’ve done wrong? Or is endRow$ not the best way to do this after all?

Thanks for your help as always!


~ZM~

Sub NamingRanges()
'
' NamingRanges Macro
'
 
' set endRow
    Range("A70000").Select
    Selection.End(xlUp).Select
    endRow$ = ActiveCell.Row
   
   
    Range("A1:AB" + endRow$).Select
    ActiveWorkbook.Names.Add Name:="Sales_Reporting_Region"
    Range("J1:AB" + endRow$).Select
    ActiveWorkbook.Names.Add Name:="Multi_Life_Name"
    Range("A1").Select
End Sub

 

[mrmmickle1]

Why don't you just do it manually like this:

=OFFSET(Sheet1!$J$1, 0, 0, COUNTA(Sheet1!$AB:$AB),19)

This is a dynamic named range. If your values are updated it will change accordingly as long as the data in AB is contiguous (does not have gaps)

Or try This:

Range("J1:AB" & EndRow$ ).Name = "YourNameHere"

 

[dangexcel]

Since you have the cells selected I believe you can use Selection.Name = "yourrangenamehere"

FYI for finding FinalRows and FinalColumns I have used the below formula's. You can use these when you don't know what the final column or row of your data is and also if the final column or row is changing this will pick it up. The 1 in the is stating which row or column to use to figure out the where the last row or column is. For example I usually use Row 1 and Column 1 because that is usually where I start my spreadsheets.

FinalRow = Cells(Rows.Count,1).End(xlup).Row

FinalCol = Cells(1,Columns.Count).End(xltoleft).Column

 

[mole999]

don't forget and may need

Dim endRow$ As Long
On Error Resume Next
ActiveWorkbook.Names("Sales_Reporting_Region").delete
endrow$ = Range("A70000").End(xlUp).Row

   Range("A1:AB" & endRow$).Select
    ActiveWorkbook.Names.Add Name:="Sales_Reporting_Region"

Range("J1:AB" + endRow$).Select make Range("J1:J" & endRow$).Select

endrows, may also been seen as LASTROW or LR

 

[zombiemaster]

Thanks, all...

I don't have time right now, but I'll try some of those suggestions and see what I come up with for more questions or problems!

~ZM~

 

[zombiemaster]

Sweet! Thanks so much to everybody – I ended up using the simple suggestion from mrmmickle1

“Range(“A1:AB” + endRow$”).Name = “Sales_Reporting_Region”

That worked perfectly and I was able to reuse the idea in a few other places as well. Thanks again, and I hope you all have a great weekend!

~ZM~

 

[mrmmickle1]

Glad you got it working alright! Happy to help.