SOLVED: Create multiple named ranges on sheet based on cell values

[lokewolf]

10325Have a spreadsheet (Report) that is 71 columns wide and hundreds of rows of data. I have one header row and the rest are variable sets of data I need to sort into named ranges. I currently have a series of Loop macros to put a # (1-8) in Col A for sorting reasons, but I need to select these groupings of rows via VBA and name them so I can custom resort each grouping. for example: starting in A2 on down the column I have 1's 2's 3's on up to...8's and I sort this ascending. I want to start in A2 and go down and say all the 1's (select entire Row and name this range as "FvOvride", 2's "FvNorm"). Any help would be very much appreciated. thanks erik

[mdmackillop]

Hi Erik,
Welcome to VBAX
Can you post some sample data with notes of your requirements. Use Manage Attachments in the Go Advanced reply section.
Regards
MD

[lokewolf]

added the sample...sized down a couple hundred rows. thanks.

[holshy]

Option Explicit

Sub FindAndNameGroups()

Dim iCounter As Integer
Dim iCurrentGroup As Integer
Dim iFirstRowNumber As Integer
Dim iLastRowNumber As Integer

Do

iCounter = iCounter + 1

If iCounter = 1 Then
iFirstRowNumber = iCounter + 1
iCurrentGroup = ThisWorkbook.Sheets("Master").Range("A1").Offset(iCounter, 0)
Else
If iCurrentGroup <> ThisWorkbook.Sheets("Master").Range("A1").Offset(iCounter, 0) Then
iLastRowNumber = iCounter
Call NameGroups(Range(iFirstRowNumber & ":" & iLastRowNumber), iCurrentGroup)
iCurrentGroup = iCurrentGroup + 1
iFirstRowNumber = iCounter + 1
End If
End If

iCurrentGroup = ThisWorkbook.Sheets("Master").Range("A1").Offset(iCounter, 0)

Loop Until ThisWorkbook.Sheets("Master").Range("A1").Offset(iCounter, 0) < 1

End Sub

Sub NameGroups(RowGroup As Range, GroupNumber As Integer)

Dim GroupName As String

Select Case GroupNumber
Case Is = 1
GroupName = "GroupName1"
Case Is = 2
GroupName = "GroupName2"
Case Is = 3
GroupName = "GroupName3"
Case Is = 4
GroupName = "GroupName4"
Case Is = 5
GroupName = "GroupName5"
Case Is = 6
GroupName = "GroupName6"
Case Is = 7
GroupName = "GroupName7"
Case Is = 8
GroupName = "GroupName8"
End Select

Names.Add GroupName, RowGroup

End Sub

I actually think the way I wrote this is kind of ugly, but it works. Keep in mind that the rows have to already be sorted by column A.

[mdmackillop]

I'm not clear on your naming convention, but this should give you the idea.

Option Explicit
Sub Naming()
Dim rng As Range
Dim ws As Worksheet
Dim i As Long
Dim Strt As Range, Endd As Range
Set ws = Sheets("Master")
With ws
Set rng = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(1))
For i = 1 To 8
Set Strt = rng.Find(i, .Cells(1, 1), xlFormulas, xlWhole, , xlNext)
Set Endd = rng.Find(i, .Cells(1, 1), xlFormulas, xlWhole, , xlPrevious)
ActiveWorkbook.Names.Add Name:="Name" & i, RefersTo:="=Master!" & Range(Strt, Endd).Resize(, 71).Address
Next
End With
End Sub

[lokewolf]

MDMACKILLOP: that worked out fantastically! Thanks for help with this!!

HOLSHY: I didn't try your approach but this time around, but I do appreciate your response! Thanks again!