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

It feels like named ranges may not be the most usable solution to whatever it is you're trying to do. What's the step after getting all the named ranges - it might be worth looking for something where you don't need to set all of those up? — Chris Geatch
Great question. This is part of a large workbook with a large number of pivot tables. The next step is to use the named ranges to easily bring in multiple values into a pivot table at once. In reality, Category 1 may have 30 or 100 values (or more). Instead of manually adding each field to a pivot table each time the workbook needs to be re-run, I wanted to simply pull in the named range into the pivot. I figure I could drag and drop 30 categories into pivots and refresh instead of manually adding thousands of values to these pivots. — ProphetofDoom

Chris Geatch Chris Geatch · Accepted Answer · 2021-02-21T19:57:50

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