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

 

[jrake40]

Thank you Rick! One last question is there a way to stick the first row of my spread sheet. It is where I input a date range so there is a date in A1 & B1 and therefore does not need to be counted.

 

[Rick Rothstein]

Thank you Rick! One last question is there a way to stick the first row of my spread sheet. It is where I input a date range so there is a date in A1 & B1 and therefore does not need to be counted.

I presume the word I highlighted in red above was supposed to be "skip", correct? If so, what cell is the first title (shown in Message #7 on? Is it cell A2 or A3? If A3, is cell A2 blank?

 

[jrake40]

Skip the first row.

A1 & B1 are populated.

Blank row after.

 

[Rick Rothstein]

Skip the first row.

A1 & B1 are populated.

Blank row after.

Okay, we just need to change the A1 to A3 in the code I posted in Message #10 then...

Sub Test()
  Dim Ar As Range, Cell As Range
  For Each Ar In Range("[B][COLOR="#FF0000"]A3[/COLOR][/B]", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants).Areas
    Ar(1).Offset(, 6).Value = Ar.Rows.Count - 2
  Next
End Sub