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

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

"Cynthia" wrote in message
news:AUsmc.42375$I%1.2758026@attbi_s51...
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

Hi William,


Application.CountA(Range("A1:P65536"))

---
Norman

"William Ryan eMVP" wrote in message
...
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

"Cynthia" wrote in message
news:AUsmc.42375$I%1.2758026@attbi_s51...
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

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

Thanks,

Bill
"Norman Jones" wrote in message
...
Hi William,


Application.CountA(Range("A1:P65536"))

---
Norman

"William Ryan eMVP" wrote in message
...
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

"Cynthia" wrote in message
news:AUsmc.42375$I%1.2758026@attbi_s51...
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

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

Cynthia wrote:
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

JWolf wrote:

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

Cynthia wrote:

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
Thanks Mr Wolf...

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

Cindi

Hi cynthia try this

=SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,Ra nge1,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

--
Message posted from http://www.ExcelForum.com