Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

# 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

Check out our Excel VBA Resources

Re: # of records

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.