Create a named range based on a value in another column not included in range

Question

I have a data set, 10 columns wide, with an ever increasing number of rows.

In column C I have a set of features, e.g. "Search" that will have a few rows corresponding to it; ""Filter" that will have a few rows corresponding to it and so on. However, these could be in any order, so I could have some "Search" features and then some "Filter" features and then some more "Search" features...

I need to create a named range for selected cells in columns D:F where the value in C is the feature I require. This would be for example a named range called "T1" that goes from D3:F6 and maybe D71:F71 for all the "Search" features, but not the "Filter" features.

I have tried using Offset and Count in the Name Manager. But ideally, I need to use VBA in my already existing macro so I don't need to go in and change the Named Ranges every time a new row is added.

Ideally the code would be along the lines of... If column C contains the word "Filter", make a named range for the three columns to the right of it, every time the word "Filter" occurs.

I used Offset and Count in the name manager:

=OFFSET(Features!$D$3, 0, 0, COUNTA(Features!$D$3:$D$9), COUNTA(Features!$D$3:$F$3))

Sub mySub()

    Dim Features As Worksheet
    Dim myNamedRange As Range

    Dim myRangeName As String
    Set Features = ThisWorkbook.Worksheets("Search")

    If Range.("C") is "Search"
    Set mRangeName= myWorksheet.Range("D:F")

    myRangeName = "Search"

    ThisWorkbook.Names.Add Name:=Search, RefersTo:=myNamedRange

End Sub

Any help would be greatly greatly appreciated. I hope I have clarified the problem enough.

Answer 1

If I understand correctly then you could try something like the following:

Sub test()
Dim featuresRng As Range
Dim rng As Range
Dim sht As Worksheet
Dim counter As Long
Dim cell As Range

Set sht = ThisWorkbook.Worksheets("Name of your worksheet")
Set featuresRng = sht.Range(sht.Range("C1"), sht.Range("C" & sht.Rows.Count).End(xlUp)) 'dynamically set the range of features
counter = 0 'this counter will help us avoid Union(Nothing, some range), which would give an error

For Each cell In featuresRng 'loop through the range of features
    If cell.Value = "search" Then
        counter = counter + 1
        If counter = 1 Then
            Set rng = sht.Range(cell.Offset(0, 1), cell.Offset(0, 3))
        Else
            Set rng = Union(rng, sht.Range(cell.Offset(0, 1), cell.Offset(0, 3))) 'build the range
        End If
    End If
Next cell
Debug.Print rng.Address
sht.Names.Add "Something", rng
End Sub

The code above, loops through the range of features and whenever a cell whose value is "search" is found, it adds the corresponding D, E and F cells in a range. In the end you have a total range which you can name whatever you want.

For example, if you have the following set-up:

Then what you'll get is this:

So the resulting range address would be $D$1:$F$2,$D$8:$F$8,$D$10:$F$12,$D$15:$F$19

Now, if you want individual named ranges to be created every time the keyword is found you can modify the code accordingly like so:

Sub test2()
Dim featuresRng As Range
Dim rng As Range
Dim sht As Worksheet
Dim counter As Long
Dim cell As Range

Set sht = ThisWorkbook.Worksheets("Name of your worksheet")
Set featuresRng = sht.Range(sht.Range("C1"), sht.Range("C" & sht.Rows.Count).End(xlUp)) 'dynamically set the range of features
counter = 0
For Each cell In featuresRng
    If cell.Value = "search" Then
        counter = counter + 1
        Set rng = sht.Range(cell.Offset(0, 1), cell.Offset(0, 3))
        sht.Names.Add "Something" & counter, rng
    End If
Next cell

End Sub