Count every 5 row that contains text or data

[mikeburg]

I'm trying to come up with the least amount of code to count every 5th
cell that contains text or data in column A starting with row 1. In
other words, the following would return a count of 3 records:

Column A
1 John Doe
2 Jiffy's
3 101Main
4 Anywhere, MI 79999
5
6 Jim Johnson
2 Jim's Trim
3 1019 Gilmer Rd
4 Jackson, TX 79555
6
7 David Smith
8 Smith MFG
9 109 E Whatley
10 Smithville, Fl 78666
11
12
13
14
15
16

Any help with the code will be greatly appreciated! mikeburg

 

[Bob Phillips]

Not sure I understand the data, but here goes

=SUMPRODUCT(--(MOD(ROW(A1:A1000),5)=1),--(A1:A1000<>""))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Do you really need code:-

Only caveat is that you can't use whole column references, so I've used 1000
as an arbitrary size.

=SUMPRODUCT(--(A1:A1000<>""),--(MOD(ROW(A1:A1000),5)=0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Ken Wright]

I assumed that starting in row 1, every 5th row was 5,10,15,20 etc

Regards
Ken..................

 

[Ken Wright]

If you really need code though then one way:-

Sub CountRows()

Dim lastrw As Long
Dim x As Long
Dim cnt As Long

lastrw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
cnt = 0

For x = 5 To lastrw Step 5
If Cells(x, 1).Value <> "" Then
cnt = cnt + 1
End If
Next x

MsgBox "Cnt = " & cnt
End Sub

Regards
Ken........................

 

[mikeburg]

Thanks everyone, y'all been a tremendous help.
Mikeburg