count rows with data
 

Need to count the number of rows that contain data - ignoring formats.
--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141

count rows with data
 

On Fri, 31 Mar 2006 14:30:02 -0800, Bryan Brassell
wrote:

Need to count the number of rows that contain data - ignoring formats.

How are you defining data? Is that any piece of information, i.e.
text, formula or number.

Does just one occurence of a piece of data in a row mean that you
count the row, or does every row have to contain data in every cell in
a range of columns before you count the row?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

count rows with data
 

any data, formulas, numbers, text, etc. One occurence in the row does count.
--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141


"Richard Buttrey" wrote:

On Fri, 31 Mar 2006 14:30:02 -0800, Bryan Brassell
wrote:

Need to count the number of rows that contain data - ignoring formats.

How are you defining data? Is that any piece of information, i.e.
text, formula or number.

Does just one occurence of a piece of data in a row mean that you
count the row, or does every row have to contain data in every cell in
a range of columns before you count the row?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

count rows with data
 

Bryan,

Try this code:

Sub RowCount()

Dim Ans As String
Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) 0 Then
Exit For
End If
Next R

Ans = MsgBox(R & " rows in worksheet.", vbOKOnly)

EndMacro:

End Sub
--
Ken Hudson


"Bryan Brassell" wrote:

Need to count the number of rows that contain data - ignoring formats.
--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141

count rows with data
 

If I mis-understodd your question and you don't want to count empty rows
interspersed throughout the worksheet, try the code below. (Credit to Chip
Pearson from whom I got the basic logic.)

Sub RowCount()

Dim Ans As String
Dim R As Long
Dim C As Range
Dim Rng As Range
Dim DataRows As Double

On Error GoTo EndMacro

DataRows = 0
If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) 0 Then
DataRows = DataRows + 1
End If
Next R

Ans = MsgBox(DataRows & " rows in worksheet.", vbOKOnly)

EndMacro:

End Sub
--
Ken Hudson


"Bryan Brassell" wrote:

Need to count the number of rows that contain data - ignoring formats.
--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141