vba to prompt for name of range, then prompt to select the range

ssurjie

New Member
Joined
Feb 21, 2014
Messages
3
Hello
I have a worksheet filled with attorney names on top cell and their telephones and faxes on the second cell (beneath their names),
I wish to convert all of these into named ranges,
so I need a macro to name each cases ( loop through all on the sheet)
Thanks, regards
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,788
Office Version
2007
Platform
Windows
Test the next macro in your sheet:

Code:
Sub Prompt_For_Name_Of_Range()
'
    For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
        u = Cells(Rows.Count, j).End(xlUp).Row
        rango = Range(Cells(1, j), Cells(u, j)).Address
        nombre = Replace(Cells(1, j).Value, " ", "_")
        hoja = ActiveSheet.Name
        ActiveWorkbook.Names.Add Name:=nombre, RefersTo:="=" & hoja & "!" & rango
    Next
    MsgBox "End"
End Sub
Regards
 

ssurjie

New Member
Joined
Feb 21, 2014
Messages
3
Thanks, Im getting an error, looks like
Compiled error
Variable not defined
I thinks that has to with Dim this... and Dim that...??
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,030
Office Version
365
Platform
Windows
How about
Code:
Sub ssurjie()
   Dim i As Long
   For i = 1 To Cells(1, Columns.Count).End(xlToLeft).column
      Range(Cells([COLOR=#ff0000]2[/COLOR], i), Cells(Rows.Count, i).End(xlUp)).Name = Replace(Cells(1, i).Value, " ", "_")
   Next i
End Sub
This will set the named range from row 2 downwards. If you want row 1 as part of the named range, change the red 2 to a 1
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,788
Office Version
2007
Platform
Windows
Updated


Code:
Sub Prompt_For_Name_Of_Range()
'
    dim j as long, u as long, rango as string, nombre as string, hoja as string
    For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
        u = Cells(Rows.Count, j).End(xlUp).Row
        rango = Range(Cells(1, j), Cells(u, j)).Address
        nombre = Replace(Cells(1, j).Value, " ", "_")
        hoja = ActiveSheet.Name
        ActiveWorkbook.Names.Add Name:=nombre, RefersTo:="=" & hoja & "!" & rango
    Next
    MsgBox "End"
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,030
Office Version
365
Platform
Windows
Not sure which of us you're talking to , but glad we could help & thanks for the feedback
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,788
Office Version
2007
Platform
Windows
I'm also glad to help
 

Forum statistics

Threads
1,086,237
Messages
5,388,636
Members
402,127
Latest member
Jemx

Some videos you may like

This Week's Hot Topics

Top