Count number of rows in multiple tabs to provide a total

[philb99]

Hi - I get sent multiple spreadsheets with multiple tabs with multiple rows of data in each - is there any way I can calculate how many rows of data are in each tab for each spreadsheet as it is received please to give a total for each spreadsheet

Thanks

 

[kweaver]

If you create a FIRST sheet called Summary, this macro will count the rows in each sheet. You can then sum the B column in Summary for the total number of rows.

Sub CountRowsPerSheet()

Dim LR As Variant
Dim WS As Worksheet
Dim WS_Count As Integer
Dim i As Integer, j As Integer

WS_Count = ActiveWorkbook.Sheets.Count
For i = 2 To WS_Count
  LR = ActiveWorkbook.Sheets(i).UsedRange.Rows.Count
  With Sheets("Summary")
  .Cells(i, 2).Value = LR
  .Cells(i, 1).Value = Sheets(i).Name
  End With
Next i

End Sub

Note: The UsedRange could possibly include blank rows. Would that give you a mis-count or will your UsedRange(s) always be contiguous rows?

If the first column in all of your sheets will indicate the row count, you could change the line that creates LR (Last Row) to:

LR = ActiveWorkbook.Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row

And, you could put in the total automatically with:

Sub CountRowsPerSheet()

Dim LR As Variant
Dim WS As Worksheet
Dim WS_Count As Integer
Dim Total As Integer
Dim i As Integer, j As Integer
Total = 0
WS_Count = ActiveWorkbook.Sheets.Count
For i = 2 To WS_Count
  LR = ActiveWorkbook.Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
' LR = ActiveWorkbook.Sheets(i).UsedRange.Rows.Count
  With Sheets("Summary")
  .Cells(i, 2).Value = LR
  .Cells(i, 1).Value = Sheets(i).Name
  Total = Total + LR
  End With
Next i
Sheets("Summary").Range("B1") = Total

End Sub

 

[philb99]

This looks very comprehensive thank you

My tabs all have the same heading and there will be no blank rows - does this mean the best macro to be would be the first one with no changes?

 

[philb99]

Hi - I have created a very simple spreadsheet with 3 rows of data - the first Macro only reported Summary = 1 and when I used the second macro Summary = either 1 or 2. Is there something else i need to be doing. Do all the tabs need to be called sheet(s)

Your help greatly appreciated

 

[kweaver]

That routine worked for me. Be sure that you have the Summary sheet as the FIRST sheet in your workbook.

The names of your sheet tabs doesn't matter.

 

[philb99]

Ah I see now Summary has to be the first sheet - Perfect

I notice that it counts the Headers, is there anyway to remove please

 

[kweaver]

Change

LR = ActiveWorkbook.Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row

to

LR = ActiveWorkbook.Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row - 1

 

[philb99]

That's fantastic - thank you