Define Named ranges via VBA Macro

[aka_krakur]

I have the following spreadsheet (that is brought in from a query):

TEMPLATEProductFamily-ReportedCode-Test.xls
	A	B	C	D
1	PRODUCT_FAMILY	PRODUCT_SEGMENT
2	Herculink	BalloonExpandStent
3	HerculinkBiliary	BalloonExpandStent
4	HerculinkElite	BalloonExpandStent
5	HerculinkEliteBiliary	BalloonExpandStent
6	HerculinkPlus	BalloonExpandStent
7	HerculinkPlusBiliary	BalloonExpandStent
8	Megalink	BalloonExpandStent
9	MegalinkBiliary	BalloonExpandStent
10	Neurolink	BalloonExpandStent
11	OTWMegalink	BalloonExpandStent
12	OTWMegalinkBiliary	BalloonExpandStent
13	Omnilink18	BalloonExpandStent
14	Omnilink18Biliary	BalloonExpandStent
15	Omnilink35	BalloonExpandStent
16	Omnilink35Biliary	BalloonExpandStent
17	UnknownOmnilink	BalloonExpandStent
18	OTWAcculink	Carotid
19	RXAcculink	Carotid
20	RXAccunet	Carotid
21	RXAccunet3-IN-1	Carotid
22	UnknownAccunet	Carotid
23	Agiltrac18	Dilatation
24	Agiltrac35	Dilatation
25	OTWViatrac	Dilatation
26	RXViatrac	Dilatation
27	RXViatracPlus	Dilatation
28	UnknownAgiltrac	Dilatation
29	Neuronet	ESClinical
30	OTWAcculinkClinical	ESClinical
31	OTWAccunetClinical	ESClinical
32	RXAcculinkClinical	ESClinical
33	RXAccunetClinical	ESClinical
34	HTSupraCore35	GuideWire
35	Spartacore	GuideWire
36	Steelcore18	GuideWire
37	Steelcore18LT	GuideWire
38	Veripath	GuidingCatheters
39	Absolute35	SelfExpandStent
40	Dynalink18	SelfExpandStent
41	Dynalink18Biliary	SelfExpandStent
42	Dynalink35	SelfExpandStent
43	Dynalink35Biliary	SelfExpandStent
44	DynalinkGrande35	SelfExpandStent
45	DynalinkGrande35Biliary	SelfExpandStent
Product_Family

1. I need to do a couple things: I need to insert "ALL" in between each Product Family (ColumnA) at each change in Product Segment (Column B)
and then fill in the blank in the Product Segment with the appropriate Product Segment
Something like this:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - TEMPLATEProductFamily-ReportedCode-Test.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION value="" selected>B31</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value=Dilatation name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">PRODUCT_FAMILY</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">PRODUCT_SEGMENT</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">ALL</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Herculink</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Herculink Biliary</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Herculink Elite</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Herculink Elite Biliary</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 1

 

[Norie]

'bails' how?

 

[aka_krakur]

Run-time error '1004':

That name is not valid.

When I click on Debug it takes me directly to

    .Range("B" & s & ":B" & rng.Row).Offset(, -1).Name = rng.Value

I can't figure out what's wrong with it.

 

[Norie]

It's because of the spaces you have in the names.

I tried this, which doesn't quite work properly as it misses out one of the ranges.

I'll try and figure that out but at least it'll give you a start.

Sub test()
Dim rng As Range
Dim s As Long
    Set rng = Range("B1")
    s = 2
    While rng.Value <> ""
        If rng.Value <> rng.Offset(1) And rng.Offset(1) <> "" Then
            rng.Offset(1).EntireRow.Insert
            rng.Offset(1, -1) = "ALL"
            rng.Offset(1) = rng.Offset(2)
            rng.Offset(1, -1).Resize(, 2).Font.Bold = True
            Range("A" & s & ":A" & rng.Row).Name = Replace(rng.Value, " ", "_")
            Set rng = rng.Offset(1)
            s = rng.Row
        End If
        Set rng = rng.Offset(1)
    Wend
End Sub

 

[aka_krakur]

That worked. Thanks Norie.

 

[Norie]

Are you sure?

The code compiles and runs fine but as I mentioned it doesn't seem to create all the required named ranges.

Or it didn't when I tried it on your sample data.

 

[aka_krakur]

Here's my final code (may be a little confusing but I tried it a few times :

Sub Macro3()
Dim x As String
    Application.ScreenUpdating = False

        ' Worksheet Tab Product_Family
        With Sheets("Product_Family")
            '   Remove Bold Font
            .Cells.Font.Bold = False
            '   Refresh Data
            .Range("A1").QueryTable.refresh BackgroundQuery:=False
           Dim rang As Range
    Set rang = Range("B1")
    
    While rang.Value <> ""
        If rang.Value <> rang.Offset(1) And rang.Offset(1) <> "" Then
            rang.Offset(1).EntireRow.Insert
            rang.Offset(1, -1) = "ALL"
            rang.Offset(1) = rang.Offset(2)
            rang.Offset(1, -1).Resize(, 2).Font.Bold = True
            Set rang = rang.Offset(1)
        End If
        Set rang = rang.Offset(1)
    Wend
'Set Defined Name Ranges for all Product_Segment
   Dim rng As Range
Dim s As Long
    Set rng = Range("B2")
    s = 2
    While rng.Value <> ""
        If rng.Value <> rng.Offset(1) And rng.Offset(1) <> "" Then
            .Range("A" & s & ":A" & rng.Row).Name = Replace(rng.Value, " ", "_")
            Set rng = rng.Offset(1)
            s = rng.Row
        End If
        Set rng = rng.Offset(1)
    Wend
        
                last9 = .Range("A65536").End(xlUp).Row
   Names.Add Name:="PRODUCTFAMILY", RefersTo:= _
    "=Product_Family!$A$2:$A$" & last9 & ""
            
    .Range("B1").End(xlDown).Offset(1, 0).FormulaR1C1 = "ALL"
    last = .Range("B65536").End(xlUp).Row
    .Range("B1:B" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range( _
        "D1"), Unique:=True
    .Range("D2:D9").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
        last10 = .Range("D65536").End(xlUp).Row
        Names.Add Name:="PRODUCTSEGMENT", RefersTo:= _
        "=Product_Family!$D$2:$D$" & last10 & ""

Only problem I run in to is the query that brings in the data somehow is gone so the line of code that refreshes the query makes the Macro stop

  .Range("A1").QueryTable.refresh BackgroundQuery:=False

I'll figure that one out though, because I have had that problem before. I just can't pinpoint why right now.
Thanks for all your help