Count of all non empty "records"

C

Cynthia

Hi all,

I would like to get a count of all non empty "records" (range of Ax:px) 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 & ":p" & intRow).Value <> "" Then
RecordCount = RecordCount + 1
Else
Exit For
End If
Next

Thank you

Cindi
 
W

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("A1:p65536")
If cell.Value <> "" Then
RecordCount = RecordCount + 1
End If

Next
MsgBox (RecordCount)
End Sub
 
W

William Ryan eMVP

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

Thanks,

Bill
 
J

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:p").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
 
C

Cynthia

JWolf said:
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:p").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
Click to expand...
Thanks Mr Wolf...

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

Cindi
 
S

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
 
Top