Auto Range Name

John117

Active Member
Joined
Sep 29, 2004
Messages
371
Hello
Col B has series of merged cells. I would like assign macro to a button to automatically create name range to these merged cells if the merge cell has any text in it (if any name exists there go to next cell).
My recorded macro so far:

Code:
Sub Make_Name()
    Range("B11:B13").Select
    ActiveWorkbook.Names.Add Name:="func1", RefersToR1C1:="=Notes!R11C2"
    ActiveWorkbook.Names.Add Name:="func2", RefersToR1C1:="=Notes!R14C2"
    ActiveWorkbook.Names.Add Name:="func3", RefersToR1C1:="=Notes!R19C2"
    Range("A11").Select
End Sub

I would like search entire Col B starting at B11for merged cells with text. In addition the # of rows in merged cells will very. Range name will be sequential Func1, Func2 , etc……
Can you please help me with this code.

Thank you
John
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Hi -
How about;
Code:
sub test()
Dim i As Long
n = 1
For i = 11 To Range("b" & Rows.Count).End(xlUp).Row
    If Cells(i, "b").Value > 0 And Cells(i, "b").MergeCells = True Then
        ActiveWorkbook.Names.Add Name:="func" & n, RefersTo:=Sheets("Notes").Cells(i, "b").Value
            n = n + 1
    End If
Next
End Sub
 

John117

Active Member
Joined
Sep 29, 2004
Messages
371
Thank you agihcam

Your code works very well with very small modification:

Rich (BB code):
Sub Range_Names()
Dim i As Long
n = 1
For i = 11 To Range("b" & Rows.Count).End(xlUp).row
    If Cells(i, "b").value > 0 And Cells(i, "b").MergeCells = True Then
        ActiveWorkbook.Names.Add Name:="func" & n, RefersTo:=Sheets("Notes").Cells(i, "b").value
        n = n + 1
    End If
Next
Range("A10").Select
End Sub
I had to remove the portion of the code highlighted in red.
Thank you very much.
John
 

Watch MrExcel Video

Forum statistics

Threads
1,113,920
Messages
5,545,029
Members
410,647
Latest member
bernardazar
Top