Dynamically Assign Named Ranges Based on Cell Values

[Richynero]

Hi there,

I have data structured as below with the first value in cell A1.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl67, width: 64"]Dog[/TD]
[TD="class: xl65, width: 64"]Bobby[/TD]
[/TR]
[TR]
[TD="class: xl67"]Dog[/TD]
[TD="class: xl65"]Dave[/TD]
[/TR]
[TR]
[TD="class: xl67"]Dog[/TD]
[TD="class: xl65"]Butch[/TD]
[/TR]
[TR]
[TD="class: xl67"]Dog[/TD]
[TD="class: xl65"]Chip[/TD]
[/TR]
[TR]
[TD="class: xl66"]Cat[/TD]
[TD="class: xl68"]Felix[/TD]
[/TR]
[TR]
[TD="class: xl66"]Cat[/TD]
[TD="class: xl68"]Tabatha[/TD]
[/TR]
[TR]
[TD="class: xl70"]Mouse[/TD]
[TD="class: xl69"]Ruby[/TD]
[/TR]
[TR]
[TD="class: xl70"]Mouse[/TD]
[TD="class: xl69"]Mark[/TD]
[/TR]
</tbody>[/TABLE]


I would like to set the range B1:B4 to a named range called 'Dog'
I would like to set the range B6:B7 to a named range called 'Cat'
I would like to set the range B8:B9 to a named range called 'Mouse'

My vba is ok but I am struggling to work out how to script the resizing of ranges based on the animal name in column A.

My real world data is several hundred rows and likely to change month on month so setting this manually, while doable, is not feasible for any long term solution.

Any help someone can give would be amazing!

Thank you

Richard

 

[Richynero]

Hi guys,

I solved it! **Crowd goes wild***

I added a helper column to column C which was a simple COUNTIF() to tell me how many rows I'd need for my named range.


Dog Bobby 4
Dog Dave 4
Dog Butch 4
Dog Chip 4
Cat Tabatha 2
Cat Felix 2
Mouse Ruby 2
Mouse Mark 2

Sub CreateNamedRanges()


i = 1


  Do Until i > 8


      shift = Cells(i, 3).Value

      ActiveWorkbook.Names.Add Name:=Cells(i, 1).Value, RefersTo:="=LUD!$B$" & i & ":$B$" & shift + i - 1

      i = i + shift


  Loop


End Sub