Define Names for multiple ranges

Question

Hi everyone,  I have a list of Categories in Col (B) of my Excel Spreadsheet.  Each of these categories has multiple rows of that same category, but each of those rows contains different referenced information, I would like to have a VBA program that steps thru the list of categories in Column (B) and would apply a Defined Range name to the next (15) Columns to it's right, but not include the Col (B), it would include all rows that are with that Category.  I'll try to provide a good example below -

16 Category(B)      (C)       (D)      (E)        (F)        (G) ---------- (Q)

17 CEEM                3PP     text       32        45        9                    58

18 CEEM                BBS     text       15        23        9                    43

19 CEEM                CBS    text         19       20        9                    45

20 DEPP                RSS    text       22          11        3                    24

etc.....

OK, so the Range C17 thru Q19 would be named CEEM, the next would be named DEPP beginning on Row 20. 

Here's the thing, I have probably 25 categories to select each range and name it that category name.

Can you help me please, I am very appreciative of your knowledge, time and effort.  Thanks - Dean

PS - I currently have both 2003 & 2010 Excel, so either or both versions are good

Answer 1

Hi,

I spotted that later and have already edited the code to start in Col C. here it is again, you will still need to change the start row

Sub Name_Ranges()

Dim LastRow As Long, Namerange As Range

Dim myRange As Range

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

Set myRange = Range("B2:B" & LastRow)

For Each c In myRange

    If c.Value <> c.Offset(1).Value Then

        If Namerange Is Nothing Then

                Set Namerange = c

        Else

                Set Namerange = Union(Namerange, c)

        End If

        ActiveWorkbook.Names.Add Name:=c.Value, RefersToR1C1:=Namerange.Offset(, 1).Resize(, 15)

        Set Namerange = Nothing

    Else

        If Namerange Is Nothing Then

                Set Namerange = c

        Else

                Set Namerange = Union(Namerange, c)

        End If

    End If

Next

End Sub

Answer 2

Hi,

I have assumed your data in Col B start in row 2 so if that's incorrect change B2 in the code (Underlined) to the correct starting row. This will work in all versions of Excel.

Sub Name_Ranges()

Dim LastRow As Long, Namerange As Range

Dim MyRange As Range

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

Set MyRange = Range("B2:B" & LastRow)

For Each c In MyRange

    If c.Value <> c.Offset(1).Value Then

        If Namerange Is Nothing Then

                Set Namerange = c

        Else

                Set Namerange = Union(Namerange, c)

        End If

       ActiveWorkbook.Names.Add Name:=c.Value, RefersToR1C1:=Namerange.Offset(, 1).Resize(, 15)

        Set Namerange = Nothing

    Else

        If Namerange Is Nothing Then

                Set Namerange = c

        Else

                Set Namerange = Union(Namerange, c)

        End If

    End If

Next

End Sub

Answer 3

Hi Mike H,  that's it.  This is all I needed.  Thanks so very much.  You've helped me before. Dean

Answer 4

Hi Mike H. Wow !!, I did replace the starting row with B17, it worked flawlessly.  One change though, I need to have it start the range in Col (C), as it is, it includes the Category Column(B) in the named range.  What would I change to exclude Column (B) in the named range?  Thx - Dean

Answer 5

Hi everyone,  I have a list of Categories in Col (B) of my Excel Spreadsheet.  Each of these categories has multiple rows of that same category, but each of those rows contains different referenced information, I would like to have a VBA program that steps thru the list of categories in Column (B) and would apply a Defined Range name to the next (15) Columns to it's right, but not include the Col (B), it would include all rows that are with that Category.  I'll try to provide a good example below -

 

16 Category(B)      (C)       (D)      (E)        (F)        (G) ---------- (Q)

17 CEEM                3PP     text       32        45        9                    58

18 CEEM                BBS     text       15        23        9                    43

19 CEEM                CBS    text         19       20        9                    45

20 DEPP                RSS    text       22          11        3                    24

etc.....

 

OK, so the Range C17 thru Q19 would be named CEEM, the next would be named DEPP beginning on Row 20. 

Here's the thing, I have probably 25 categories to select each range and name it that category name.

 

Can you help me please, I am very appreciative of your knowledge, time and effort.  Thanks - Dean

 

PS - I currently have both 2003 & 2010 Excel, so either or both versions are good

Assuming that you know the first and last row of you Categories list, try this macro:

Sub define_named_ranges(first_row As Integer, last_row As Integer)

  Dim current_row As Integer

  current_row = first_row

  While current_row <= last_row

    While Cells(current_row, "B") = Cells(first_row, "B") And current_row <= last_row

      current_row = current_row + 1

    Wend

    ActiveWorkbook.Names.Add Name:=Cells(first_row, "B"), RefersTo:=Cells(first_row, "C").Resize(current_row - first_row, 15)

    first_row = current_row

  Wend

End Sub

Hope this helps / Lars-Åke