# of records

Posted by R.Brayton on June 30, 2001 10:22 PM

Does anyone know how to calculate the total number of rows on multple worksheets within a workbook? Also how can I consolidate the records so I have less worksheets? This one's probably a no brainer but I've been stuck for days now. Thanks

Posted by mseyf on July 02, 2001 11:45 AM

to count the records, you can try something like this:

Sub CountRecords()
Dim shtSheet As Worksheet
Dim intRecCount As Integer
intRecCount = 0
For Each shtSheet In Sheets
intRecCount = intRecCount + shtSheet.UsedRange.Rows.Count - 1 'subtract 1 for heading
Next
MsgBox intRecCount
End Sub

this macro assumes that you want to count the number of used rows (minus 1 for the heading row) and that you want to count the rows on each sheet. If you don't want to count each sheet, you could setup an array of sheets you want to count.

Hope this can get you started.

Mark