Loop Counting - Stop and Start based on a blank row

[jrake40]

I would like to loop count different sets of data. They are separated by a blank. Here is the VBA i have for counting but I want to make it loop for each data set.

Sub RowCount()

Dim countnonblank As Integer, myRange As Range
Set myRange = Columns("A:A")
countnonblank = Application.WorksheetFunction.CountA(myRange)


Dim theCount
theCount = countnonblank - (Application.WorksheetFunction.CountIf(Range("A:AA"), "Title") * 2 + 1)
MsgBox "Data Rows " & theCount


End Sub

 

[Rick Rothstein]

I would like to loop count different sets of data. They are separated by a blank.

Are there any formulas in Column A?

 

[jrake40]

There are not any formulas in column A.

 

[Rick Rothstein]

There are not any formulas in column A.

Okay, see if this construction helps you any. I'm not sure what coding you want to apply to the cells within each area (contiguous range) in Column A, so I just gave you hints in comments as to what is going on...

[table="width: 500"]
[tr]
	[td]Dim Ar As Range, Cell As Range
For Each Ar In Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants).Areas
  '
  '  This outer loop will iterate each area (contiguous range)
  '  individually within the overall non-contiguous range of cells
  '
  For Each Cell In Ar
    '
    '  This inner loop will iterate each cell individually
    '  within each contiguous range of constants within each
    '  individual area of the overall non-contiguous range
    '
  Next
Next[/td]
[/tr]
[/table]

 

[jrake40]

Awesome. I think this is exactly what I need just need to figure out how to fit my counting into it and then end the loop at the last row. If you have any further suggestions I greatly appreciate the help!

 

[Rick Rothstein]

Awesome. I think this is exactly what I need just need to figure out how to fit my counting into it and then end the loop at the last row. If you have any further suggestions I greatly appreciate the help!

It would be easier to comment if we knew what your data layout (all your data) was and exactly what you wanted your code to do with each area's cells as it relates to your overall data layout.

 

[jrake40]

Sure thing Rick. Here is a small subset of my data. This data layout repeats throughout the spreadsheet (always one blank row between data sets). The only thing that varies is how many rows appear under the title and column headers.

[TABLE="class: grid, width: 464"]
<tbody>[TR]
[TD="colspan: 2"]Failed XMLs in TRAN LOG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tran_log_id[/TD]
[TD]origin_id[/TD]
[TD]reference_id[/TD]
[TD]result_code[/TD]
[TD]last_updated_dttm[/TD]
[/TR]
[TR]
[TD="align: right"]11421022[/TD]
[TD]OMS[/TD]
[TD]OMS_39794575[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]01:30.7[/TD]
[/TR]
[TR]
[TD="align: right"]11421023[/TD]
[TD]OMS[/TD]
[TD]OMS_39794575[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]01:47.2[/TD]
[/TR]
[TR]
[TD="align: right"]11421024[/TD]
[TD]OMS[/TD]
[TD]OMS_39794575[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]01:59.7[/TD]
[/TR]
[TR]
[TD="align: right"]11421025[/TD]
[TD]OMS[/TD]
[TD]OMS_39794575[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]02:13.6[/TD]
[/TR]
[TR]
[TD="align: right"]11421026[/TD]
[TD]OMS[/TD]
[TD]OMS_39794575[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]02:26.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Locked Cases in putaway status[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lpn[/TD]
[TD]Lock code[/TD]
[TD]location[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]i020000529275[/TD]
[TD]RV[/TD]
[TD]A 20 43 128 7 B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]i020000759558[/TD]
[TD]DM[/TD]
[TD]A 20 39 131 1 B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]i020000662804[/TD]
[TD]WO[/TD]
[TD]A 20 48 007 2 B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]i020000692847[/TD]
[TD]QA[/TD]
[TD]A 20 26 006 4 B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]i020000689885[/TD]
[TD]QA[/TD]
[TD]A 20 47 048 4 E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]i020000757677[/TD]
[TD]DM[/TD]
[TD]A 20 39 127 1 A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Basically I want to count each sets rows that have data in them. So count each data set minus 2 since I don't want to count the title and headers. So for the first set I want a count of 5 and for the second set a count of 6. This will then continue down until the last row. The counts can be output into any cell for now.

 

[Rick Rothstein]

Sure thing Rick. Here is a small subset of my data. This data layout repeats throughout the spreadsheet (always one blank row between data sets). The only thing that varies is how many rows appear under the title and column headers.

Your posted picture shows your titles in merged cells... is that, in fact, what you have? If so, because the merged cells span ranges with multiple columns, they are seen different areas from the cells they are attached to in Column A... special coding will be needed to work around them. Just so you know for future reference... merged cells cause all kinds of problems in code (for example, if you have one title that is short and, hence, its cell is not merged, that cell will be grouped with the cells in a single area whereas title merged with other cells won't... this possible variation between what should be like items is one of the factors that make merged cells problematic (there are others as well). The alternative to merged cells is selecting the same cells you would when merging, only you don't merge them, rather, you format them with the "Center Across Selection" horizontal text alignment option on the Alignment tab of the Format Cells dialog box... doing it this way keeps all cells as individual cells thus avoiding the problems that merged cells cause.

 

[jrake40]

Sorry the titles are not merged. I am sorry for not noticing this before I posted. My apologies I'm new to the group and wasn't sure of the best way to show some of my data.

 

[Rick Rothstein]

Sorry the titles are not merged. I am sorry for not noticing this before I posted. My apologies I'm new to the group and wasn't sure of the best way to show some of my data.

No problem... I just needed to know so that I would know how to write the code. As it turns out, you only need one loop to count the cells within each area... range objects have a SpecialCells property (the VBA equivalent of the "Special..." button on the "Go To" dialog box [F5 key]) which lets you create the non-contiguous range of areas that can be iterated and each area, being contiguous, has a Rows.Count property which tells you how many rows are in the area... we just have to subtract 2 from it so that the title and headers do not get counted. Here is a macro that does what you want and outputs the count to Column G on the same row as the titles....

[table="width: 500"]
[tr]
	[td]Sub AreaRowCounts()
  Dim Ar As Range, Cell As Range
  For Each Ar In Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants).Areas
    Ar(1).Offset(, 6).Value = Ar.Rows.Count - 2
  Next
End Sub[/td]
[/tr]
[/table]