Auto Range Name

[John117]

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:

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

 

[agihcam]

Hi -
How about;

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]

Thank you agihcam

Your code works very well with very small modification:

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