How to get number of all rows in every datasheet together?

  • Thread starter Thread starter Jan Kratochvil
  • Start date Start date
J

Jan Kratochvil

I have to count all rows in every datasheet and than get the sum of all rows
of the Excel file, that contain data.

It is a large Excel file with about 80 datasheets so do it manuall is not
possible.

Thank you
 
You could use a macro that loops through all the worksheets in that workbook.
This basis the count on the number of used rows in column A:

Option Explicit
Sub testme()
dim TotalRows as long
dim wks as worksheet

totalrows = 0
for each wks in activeworkbook.worksheets
totalrows = totalrows + application.counta(wks.range("A:A"))
next wks

msgbox totalrows
End sub

You could replace that msgbox with something like:

worksheets("Somesheetnamehere").range("x99").value = totalrows

Change the name of the worksheet and the address of the cell to what you want.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Back
Top