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

 

[bobsan42]

I would think that if you're looking for Adam you should get a list like A1, A7. But you want to include the next blank cell as well? And do you only want to get the addresses of the cells or something else too?
I will not pretend to understand what your code is trying to accomplish. Your code - it does some things but does not really put them together, so I can't really get the idea.
Is the screenshot from the Activesheet or the refsheet?
Refsheetname - it is bound to be "a6". So yo can skip this line and make the next one:

Set refSheet = Worksheets("a6")

So you must have a sheet called a6.
The for loop is ok although i would limit the number of cells by using intersect of ranges:

For Each cell In intersect(refSheet.Range("A:A"), refsheet.usedrange)

An alternative is to use the .Find method on this range. then if the resulting range is not nothing repeat .Findnext until you return to the first cell you found. The ms doc on these methods is a good starting point.

 

[bobsan42]

Part 2:
I am typing on my phone so excuse me if i make some mistake. And the space is limited...

Simplify the value checking - you only need to check the value for County:

If cell.Value = County Then

If it is empty it will return False.
Then you create a cople of lists and do nothing with them. What is it you need to do?
Which information do you need from the cells you find?
Is it the address? I would not think it is the value. Or is it information/value from adjacent cell?

Why have you chosen to create the arrays in this way? What is the final game?

 

[Peter_SSs]

Welcome to the MrExcel board!

Are the cells between the names in column A actually blank in your real data or is there other data in those rows?
If other data, can you give us a more realistic sample, including anything that might be in column B?

Assuming blank, is it always only one blank row after each name? (If not, how would we determine how many blanks after "Duke" in cell A9?)

so that I can sort the data in related columns

If that is your goal, perhaps creating a helper column as below and use that to sort?

Sub SortHelper()
  Dim lr As Long, nc As Long
  
  lr = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  nc = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  With Cells(2, nc).Resize(lr - 1)
    .FormulaR1C1 = "=IF(RC1="""",R[-1]C,RC1)"
    .Value = .Value
  End With
End Sub

For sample data in columns A:B below, the code produced column C, which could then be used for sorting.

theunsigned.xlsm
	A	B	C
1	Name	Data
2	Adam	a	Adam
3		b	Adam
4	Duke	c	Duke
5		d	Duke
6	Duke	e	Duke
7		f	Duke
8	Adam	g	Adam
9		h	Adam
10	Duke	i	Duke
11		j	Duke
12		k	Duke
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF($A2="",C1,$A2)

 

[theunsigned]

Welcome to the MrExcel board!

Are the cells between the names in column A actually blank in your real data or is there other data in those rows?
If other data, can you give us a more realistic sample, including anything that might be in column B?

Assuming blank, is it always only one blank row after each name? (If not, how would we determine how many blanks after "Duke" in cell A9?)


If that is your goal, perhaps creating a helper column as below and use that to sort?

Sub SortHelper()
  Dim lr As Long, nc As Long
 
  lr = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  nc = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  With Cells(2, nc).Resize(lr - 1)
    .FormulaR1C1 = "=IF(RC1="""",R[-1]C,RC1)"
    .Value = .Value
  End With
End Sub

For sample data in columns A:B below, the code produced column C, which could then be used for sorting.

theunsigned.xlsm
	A	B	C
1	Name	Data
2	Adam	a	Adam
3		b	Adam
4	Duke	c	Duke
5		d	Duke
6	Duke	e	Duke
7		f	Duke
8	Adam	g	Adam
9		h	Adam
10	Duke	i	Duke
11		j	Duke
12		k	Duke
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF($A2="",C1,$A2)

Hi, Thank you so much for your reply!

There is always a blank cell below the county name in column A, the data is all over the place: sometimes the county is repeated, sometimes it's a different county and then goes back to a previous one. I'm not sure I'm approaching this the right way but I thought I would be able to collect the ranges between county names, then loop through them to gather the other data I need, which falls between those rows in different columns using offsets.

I'll include a more expansive example of the data sheet I'm working from, which will hopefully answer your questions:

housing by county.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														# of Beds	Beds Occupied	Beds Vacant	Occupancy Rate
2	Adam	Supported/Single Room Occupancy (SRO)					7418		Assoc/Adam-Bluee Cos., Inc					25	25	0	100.0%
3							037		Blueport Garden SP/SRO
4									8 Blue Acres Road Hudson, CO, 34
5								Supported/Single Room Occupancy (SRO) Totals:						25	25	0	100.0 %
6								Adam Non-Certified Totals:						25	25	0	100.0 %
7								Adam County Totals:						25	25	0	100.0 %
8	Duke	Congregate/Treatment					6061		Gateway Community Industries, Inc.					12	11	1	91.7%
9							433		Village Group Home
10									1 Wodenethe Drive Village, CO, 083912
11							7144		Support Services, Inc.					12	12	0	100.0%
12							438		Osborne Road Group Home
13									131 Osborne Hill Road Troutriver, CO, 242509
14							7144		Support Services, Inc.					14	14	0	100.0%
15							409		Rad - South Randolph Group Home
16									100 South Randolph Avenue Town, CO, 015124
17							0016		City Psychiatric Center					24	24	0	100.0%
18							852		Highview Group Home
19									264 Hudson View Drive Town, CO, 01
20								Congregate/Treatment Totals:						62	61	1	98.4 %
21	Duke	Apartment/Treatment					7144		Support Services, Inc.					36	21	15	58.3%
22							401		Rad - Duke Apartments
23									510 Haight Avenue (Office) Town, CO, 03
24								Apartment/Treatment Totals:						36	21	15	58.3 %
Sheet1

Each month, I run this report, which has roughly 1000 rows of this and the county information is all mixed up. So, I need to find the ranges of each county, sort by the top numbers in column G, display the name in column I one row down from that number in G, and display the number in column P on the same row as that found in column G. I name the data sheet "a6" for adult housing for the 6th month of the year.

I imagined being able to create a list of starting points, then iterate through that list to find endpoints (the next county), create a list of ranges, then use that list of ranges to sift through the data I need, sort it, and display it in a new sheet. But, there's no i++ in VBA, right?

Thanks again, I love learning about this and can't wait to utilize it more once I'm more knowledgeable. I really appreciate your insights.

 

[theunsigned]

Part 2:
I am typing on my phone so excuse me if i make some mistake. And the space is limited...

Simplify the value checking - you only need to check the value for County:

If cell.Value = County Then

If it is empty it will return False.
Then you create a cople of lists and do nothing with them. What is it you need to do?
Which information do you need from the cells you find?
Is it the address? I would not think it is the value. Or is it information/value from adjacent cell?

Why have you chosen to create the arrays in this way? What is the final game?

Thank you so much for your help, I really appreciate it! I posted a response above that explains my final game as you requested. Thank you again and all the best.

 

[Peter_SSs]

I need to find the ranges of each county, sort by the top numbers in column G, display the name in column I one row down from that number in G, and display the number in column P on the same row as that found in column G.

Using the sample data from post #5, could you perform (manually) the steps outlined in what I have quoted here and post the resultant sheet again with XL2BB?

 

[theunsigned]

Using the sample data from post #5, could you perform (manually) the steps outlined in what I have quoted here and post the resultant sheet again with XL2BB?

Good morning, I hope you had a nice weekend.

Thank you for your continued assistance. The data from the earlier post should ultimately break out into two sheets and look like this:

Duke:

housing by county.xlsm
	A	B	C	D	E	F	G	H	I	J
4	PROVIDER	Housing Type	Name/Site or Category	Capacity	Jan	Feb	Mar	Apr	May	June
5	Gateway Community Industries, Inc.	Congregate/Treatment	Village Group Home	12						1
6	Support Services, Inc.	Congregate/Treatment	Osborne Road Group Home	12						0
7			Rad - South Randolph Group Home	14						0
8		Apartment/Treatment	Rad - Duke Apartments	36						15
9	City Psychiatric Center	Congregate/Treatment	Highview Group Home	24						0
Duke

Adam:

housing by county.xlsm
	A	B	C	D	E	F	G	H	I	J
4	PROVIDER	Housing Type	Name/Site or Category	Capacity	Jan	Feb	Mar	Apr	May	June
5	Mental Health Assoc/Columbia-Greene Cos., Inc	Supported/Single Room Occupancy (SRO)	Blueport Garden SP/SRO	25						0
Adam

Thank you again!

 

[Peter_SSs]

I'm afraid that I can't see a direct logical connection or how to logically/consistently get those results from that data.

 

[theunsigned]

I'm afraid that I can't see a direct logical connection or how to logically/consistently get those results from that data.

No worries, thank you for taking a look. In C#, I would get the row for each county listed, search down until there's another one, collect the rows in between into a list of ranges, and iterate through to sift through the offset cell information but I can't seem to figure out looping through a list similar to i++ loops.

Thanks again, all the best!