Count of all non empty "records"

[Cynthia]

Hi all,

I would like to get a count of all non empty "records" (range of Axx) and tried to use this code,
but it keeps returning 32767...is this the correct way?

intRow = 2
RecordCount = 0

For i = intRow To Rows.Count
If Sheet2.Range("A" & intRow & "" & intRow).Value <> "" Then
RecordCount = RecordCount + 1
Else
Exit For
End If
Next

Thank you

Cindi

 

[William Ryan eMVP]

You may want to try this (You can also use the Offset function to move
around cells):

Public Sub Test()
Dim ntRow As Integer
Dim RecordCount As Integer
Worksheets(1).Select
ntRow = 2
RecordCount = 0
For Each cell In Range("A165536")
If cell.Value <> "" Then
RecordCount = RecordCount + 1
End If

Next
MsgBox (RecordCount)
End Sub

 

[Norman Jones]

Hi William,


Application.CountA(Range("A165536"))

 

[William Ryan eMVP]

Right on brother. My VBA is quite rusty but I'm getting in back.

Thanks,

Bill

 

[JWolf]

Try this:

Sub test()
Dim lastrow As Long, i As Long, recordcount As Long
Dim j As Integer
Dim msg As String
lastrow = Range("a").Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
For i = 2 To lastrow
For j = 1 To 16
If Cells(i, j).Value <> "" Then
recordcount = recordcount + 1
Exit For
End If
Next j
Next i
msg = "There are " & recordcount & " rows below " _
& "row 1 which contain a non-blank cell in columns A to P."
MsgBox (msg)
End Sub

 

[Cynthia]

Try this:

Sub test()
Dim lastrow As Long, i As Long, recordcount As Long
Dim j As Integer
Dim msg As String
lastrow = Range("a").Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
For i = 2 To lastrow
For j = 1 To 16
If Cells(i, j).Value <> "" Then
recordcount = recordcount + 1
Exit For
End If
Next j
Next i
msg = "There are " & recordcount & " rows below " _
& "row 1 which contain a non-blank cell in columns A to P."
MsgBox (msg)
End Sub

Thanks Mr Wolf...

That does return the number of rows that do not contain any data!

Cindi

 

[Simon Lloyd]

Hi cynthia try this

=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),
IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))

This formula will return the value of all unique non blank entries i
the range....i have used this.

HTH

Simo