collect ranges into list under conditions

[theunsigned]

Hi, I'm brand new to VBA but I have some C# experience; I have done quite a bit of research but I'm curious how I might accomplish the following:

I have a large excel file with the names of counties in column A and I want to create a list of ranges so that I can sort the data in related columns and display it in a different format. I have been able to loop through column A and pick out the cells that contain the variable county name but am struggling with the next part, which is setting the range between that starting cell and the next cell in the column that is not empty. The counties and related data are all mixed up and I want to sort it by county in another sheet.

	A
1	Adam
2
3	Duke
4
5	Duke
6
7	Adam
8
9	Duke

So, in that example, if I want a list of ranges for "Adam," then I'd be looking for (A1:A2, A7:A8), right? I've looked into quite a lot but am not sure on how to loop properly or use the find methods to achieve my goal.

So far, my working code is the following (please be kind, I know I need to shape it up)

Dim County As String
Dim refSheetname As String
Dim refSheet As Worksheet
Dim CoStartList As Object
Dim CoRangesList As Object
Dim nextfilled As Range
Dim chunk As Range

Sub CallSubs()

    Call getInfo
    
End Sub


Sub getInfo()
    ''GET COUNTY NAME FROM SHEET NAME
    County = ActiveSheet.Name

    ''GET NAME OF REFERENCE SHEET - need to make dynamic
    refSheetname = Sheets("a" & 6).Name

    ''SET REFERENCE SHEET
    Set refSheet = Worksheets(refSheetname)

    ''GET CELLS in refsheet range A:A that match County
    Set CoStartList = CreateObject("System.Collections.ArrayList")
    For Each cell In refSheet.Range("A:A")
        If Not IsEmpty(cell) And cell.Value = County Then
            CoStartList.Add cell   
        End If
    Next
    
    Set CoRangesList = CreateObject("System.Collections.ArrayList")
    
End Sub

Any assistance would be greatly appreciated. Thank you! Please let me know how I can clarify more

 

[Peter_SSs]

I want to create a list of ranges

So let's go back to this. I have assumed
- that each range will consist of at least two rows, one of which will be blank in column A
- that column N can be used to determine the end of the data.

See if this is any use.

Sub CollectRanges()
  Dim d As Object
  Dim rA As Range
 
  Set d = CreateObject("Scripting.Dictionary")
  For Each rA In Range("A2:A" & Range("N" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
    With rA
      d(.Cells(0).Value) = d(.Cells(0).Value) & ";" & .Offset(-1).Resize(.Rows.Count + 1).Address(0, 0)
    End With
  Next rA
  With Range("AA2:AB2").Resize(d.Count)
    .Value = Application.Transpose(Array(d.Keys, d.Items))
    .Columns(2).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
    .Rows(0).Value = Array("County", "Range(s)")
    .EntireColumn.AutoFit
  End With
End Sub

My sample data with results in columns AA:AB

theunsigned.xlsm
	A													N												Z	AA	AB
1	Name													data													County	Range(s)
2	Adam													data													Adam	A2:A3;A10:A11
3														data													Duke	A4:A7;A8:A9;A12:A14
4	Duke													data
5														data
6														data
7														data
8	Duke													data
9														data
10	Adam													data
11														data
12	Duke													data
13														data
14														data
Sheet1

 

[theunsigned]

So let's go back to this. I have assumed
- that each range will consist of at least two rows, one of which will be blank in column A
- that column N can be used to determine the end of the data.

See if this is any use.

Sub CollectRanges()
  Dim d As Object
  Dim rA As Range
 
  Set d = CreateObject("Scripting.Dictionary")
  For Each rA In Range("A2:A" & Range("N" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
    With rA
      d(.Cells(0).Value) = d(.Cells(0).Value) & ";" & .Offset(-1).Resize(.Rows.Count + 1).Address(0, 0)
    End With
  Next rA
  With Range("AA2:AB2").Resize(d.Count)
    .Value = Application.Transpose(Array(d.Keys, d.Items))
    .Columns(2).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
    .Rows(0).Value = Array("County", "Range(s)")
    .EntireColumn.AutoFit
  End With
End Sub

My sample data with results in columns AA:AB

theunsigned.xlsm
	A													N												Z	AA	AB
1	Name													data													County	Range(s)
2	Adam													data													Adam	A2:A3;A10:A11
3														data													Duke	A4:A7;A8:A9;A12:A14
4	Duke													data
5														data
6														data
7														data
8	Duke													data
9														data
10	Adam													data
11														data
12	Duke													data
13														data
14														data
Sheet1

Thank you for this, it will take me a bit to be able to work on this more but it looks like you cleared up a good amount of my confusion, thank you again.