Help needed naming Ranges with VBA

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
197
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~



Code:
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
Why don't you just do it manually like this:

Code:
=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:

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

dangexcel

New Member
Joined
Oct 9, 2014
Messages
21
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
don't forget and may need
Rich (BB code):
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

Board Regular
Joined
Oct 27, 2009
Messages
197

ADVERTISEMENT

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~
:cool:
 

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
197
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~
:cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,425
Messages
5,528,687
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top