Name Multiple Ranges - Using Data in Two Columns

Question

I searched for this with no luck. Any help is appreciated.

Data Set

I have a fairly tall dataset (roughly between 1,000 and 5,000 rows) with two columns; one for category and the other for values. The data varies; there might be 10 categories, maybe 100, or any other number:

Column A	Column B
Category 1	Value 1
Category 1	Value 2
Category 1	Value 3
Category 2	Value 4
Category 2	Value 5
Category 2	Value 6
Category 3	Value 7
Category 3	Value 8
Category 3	Value 9
Category 3	Value 10

etc.

Objective

I would like to create ranges based on the values in Column A that only include the values in Column B, using the value of Column A as the name of the range.

So for example:

• Range Name = "Category 1" ; Range Values { Value 1, Value 2, Value 3}
• Range Name = "Category 2" ; Range Values { Value 4, Value 5, Value 6}
• Range Name = "Category 3" ; Range Values { Value 7, Value 8, Value 9, Value 10}
• etc.

Things Attempted

I've tried a number of things, but I'm such a VBA novice that I can't tell what is an effective way to approach this solution and fear I keep making wrong turns in the approach.

I thought perhaps trying to find the next distinct value in Column A might be a good idea paired with the offset function, but I can't get much further than simply creating and naming the range of categories to look through in Column A. I'm sure this isn't necessary, but I am definitely not a VBA expert.

Sub AllCategories

Dim sht As Worksheet  
Dim lrow As Long  
Dim r As Range  

Set sht = Sheets("Sheet1")  
lrow = sht.Cells(Rows.Count, "A").End(xlUp).Row  
Set r = sht.Range("A2:A" & lrow)  

ActiveWorkbook.Names.Add _  
   Name:="AllCategories", _  
   RefersTo:=r  

End Sub

Ideas?

It seems like I'll need a start variable and end variable for the categories, but I can't find how to look for the next distinct value. Maybe some Find variable function that loops?

Thanks for any help, it is much appreciated.

Cheers, Prophet

Answer 1

You could set up the lists without VBA if you have O365.

D1 =UNIQUE(A1:A10)
E1 =TRANSPOSE(UNIQUE(FILTER($B$1:$B$10,$A$1:$A$10=D1)))

Copy E1 down.

Then if you still needed names you could run this.

Sub MakeNames()

    Dim rng As Range
    Dim newName As String

    For Each rng In Range("D1#")
        newName = Replace(rng.Value2, " ", "_")
        ActiveWorkbook.Names.Add Name:=newName, RefersTo:="=" & rng.Offset(0, 1).Address & "#"
    Next
End Sub  

Answer 2

Instead of using defined names, you can simply use Excel's AutoFilter under Data >> AutoFilter to get the results as shown below. Then, you can use a VBA macro to automatically select the Category as shown in the last selection. In this example, Category 2 is selected. The VBA code to make the table illustration is:

Selection.AutoFilter Field:=1, Criteria1:="Category 2"

Answer 3

As per my comment, I'm not sure whether what you're doing is the best way of doing things, and since you can't have spaces in the names of named variables, if your categories are actually going to be words and some could have spaces, you're setting up a lot of heartache processing exceptions. That aside, if it's really what you need, you weren't that far off, but you can't keep track of the start and end of each range with one variable. The code below will do what you've asked for:

Sub name_ranges()
    Dim iRow As Integer
    Dim iStart As Integer
    Dim rang As Range
    
    iRow = 2
    iStart = 2
    
    With Worksheets("test")
        While .Cells(iRow, 1) <> ""
            iRow = iRow + 1
            If .Cells(iRow, 1) <> .Cells(iRow - 1, 1) Then
                Set rang = Intersect(Columns(2), Rows(iStart & ":" & iRow - 1))
                rang_name = Replace(.Cells(iRow - 1, 1), " ", "")
                ActiveWorkbook.Names.Add Name:=rang_name, RefersTo:="=test!" & rang.Address
                iStart = iRow
            End If
        Wend    
    End With
End Sub