collect ranges into list under conditions

theunsigned

New Member
Joined
Jul 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
1Adam
2
3Duke
4
5Duke
6
7Adam
8
9Duke

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)

VBA Code:
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
 
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.

VBA Code:
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
ANZAAAB
1NamedataCountyRange(s)
2AdamdataAdamA2:A3;A10:A11
3dataDukeA4:A7;A8:A9;A12:A14
4Dukedata
5data
6data
7data
8Dukedata
9data
10Adamdata
11data
12Dukedata
13data
14data
Sheet1
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.

VBA Code:
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
ANZAAAB
1NamedataCountyRange(s)
2AdamdataAdamA2:A3;A10:A11
3dataDukeA4:A7;A8:A9;A12:A14
4Dukedata
5data
6data
7data
8Dukedata
9data
10Adamdata
11data
12Dukedata
13data
14data
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top