count rows with data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Bryan Brassell
Padgett Business Services
281-897-9141
 
Need to count the number of rows that contain data - ignoring formats.
Click to expand...

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
__________________________
 
any data, formulas, numbers, text, etc. One occurence in the row does count.
--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141
 
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).EntireRow) > 0 Then
Exit For
End If
Next R

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

EndMacro:

End Sub
 
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).EntireRow) > 0 Then
DataRows = DataRows + 1
End If
Next R

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

EndMacro:

End Sub
 
Back
Top