|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» Count cells in a range from all worksheets using VBA in Microsoft Excel
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
It's possible to achieve this with the built-in COUNT() or COUNTA() function, but that can be a big task if the workbook contains many worksheets,or if you frequently add new worksheets to the workbook. To count a range from several worksheets you can use the COUNTA-function like this: =COUNTA(Sheet1:Sheet10!A1:A100) The formula above will count all non-blank cells in the range A1:A100 in the worksheets Sheet1 to Sheet10. With the custom function below it's easy to count cells in a range from all the worksheets in a workbook:
Function CountAllWorksheets(InputRange As Range, InclAWS As Boolean) As Double
' counts the content of InputRange in all worksheets in the workbook
Dim ws As Worksheet, TempCount As Long
Application.Volatile True ' calculates every time a cell changes
TempCount = 0
For Each ws In ActiveWorkbook.Worksheets
If InclAWS Then ' include the active worksheet
TempCount = TempCount + _
Application.WorksheetFunction.Count(ws.Range(InputRange.Address))
Else ' exclude the active worksheet
If ws.Name <> ActiveSheet.Name Then ' not the active worksheet
TempCount = TempCount + _
Application.WorksheetFunction.Count(ws.Range(InputRange.Address))
End If
End If
Next ws
Set ws = Nothing
CountAllWorksheets = TempCount
End Function
This function is used in the same way as built-in worksheetfunctions. In Excel 5/95 the variable InputRange must be defined as Variant instead of Range. This function can easily be adapted for use with other worksheetfunctions that you wish to use on all the worksheets in a workbook. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider
Microsoft Windows XP Inside Out
Getting to Yes: Negotiating Agreement Without Giving In
Windows XP for Dummies
Financial Risk Manager Handbook, Second Edition | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|