Excel Row Count Per Sheet

Question

I'm currently using the code below to count all the rows in each sheet and it prints on the main sheet of the workbook. Now, I'm trying to print the amount of rows used on each sheet in the last row of that specific sheet and on the main sheet as well.

    Function Test_It()
    Dim printRow As Integer
    printRow = 2
    For Each Sheet In ThisWorkbook.Sheets
        Range("N" & printRow).Value = "Sheet Name:"
        Range("O" & printRow).Value = Sheet.Name
        Range("P" & printRow).Value = "Count:"
        Range("Q" & printRow).Value = CountMyRows(Sheet.Name)
        printRow = printRow + 1
    Next Sheet
End Function


Function CountMyRows(SName As String) As Long         '# where SName is the name of a sheet
    Dim RowCount As Long
    RowCount = ThisWorkbook.Worksheets(SName).UsedRange.Rows.Count - 1
    CountMyRows = RowCount
End Function

Any help is appreciated, thanks in advance!

Code that didn't work

Sub LineCount()

    Dim ws As Worksheet
    Dim RowCount As Integer
    Dim countTotal As Long
    Dim myArray() As Variant

    RowCount= ActiveWorkbook.Worksheets.Count
    countTotal = RowCount
    ReDim myArray(1 To RowCount)

    For i = 1 To RowCount
        countTotal = ""
        myArray(1) = Worksheets(i).UsedRange.Rows.Count
        Debug.Print myArray(1)
        Range("A" & countTotal).Value = countTotal
    Next


End Sub

This is the goal:

Main sheet: this is the main sheet where all the rows used reside

Sheet 2: sheet 2 contains other related data

Sheet 3: sheet 3 contains other related data

Sheet 4: sheet 4 contains other related data

Final code I got to work

Sub LineCount()

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

For Each sht In ThisWorkbook.Worksheets
    dict(sht.Name) = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
    sht.Range("A" & Rows.Count).End(xlUp).Offset(2, 0) = "Rows Used: " & dict(sht.Name)
Next sht

With Sheet1
    .Range("A1").Resize(dict.Count).Value = Application.Transpose(dict.Keys)
    .Range("B1").Resize(dict.Count).Value = Application.Transpose(dict.Items)
End With


End Sub

The only issue now is that when the button is pressed multiple times, it will recount the rows from 14 rows to now 28 rows and so on (depending on how many times the button is pressed). How could this be resolved?

What is your exact question? What error/issue are you having converting your previous code to your new intent? if not currently presented, please state what you've tried, and give examples where applicable. — Cyril, Jan 02 '20 at 15:45
There are [better ways to count rows](https://stackoverflow.com/q/11169445/9758194) — JvdV, Jan 02 '20 at 15:45
`LastRow = ActiveWorkbook.Worksheets.Count` - is this actually what you have? That is a confusing variable name for a worksheet count. Also you can't do `countTotal = " "` if `countTotal` is a `Long`. — BigBen, Jan 02 '20 at 15:54
Well please fix that naming then - for our sanity and yours :-) — BigBen, Jan 02 '20 at 15:56

score 0 · Answer 1 · answered Jan 02 '20 at 16:22

0

Something fairly basic could be:

Sub Test()

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

For Each sht In ThisWorkbook.Worksheets
    dict(sht.Name) = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
Next sht

With Sheet1
    .Range("A2").Resize(dict.Count).Value = Application.Transpose(dict.Keys)
    .Range("B2").Resize(dict.Count).Value = Application.Transpose(dict.Items)
End With

End Sub

Implement an If condition on the sht.Name property if you want to avoid including the "Main" sheet, whichever that is in your case.

answered Jan 02 '20 at 16:22

JvdV

70,606
8
39
70

This code works but it prints the sheet name on only sheet1, I need it on all the sheets + 1 row from the last row used. Like on sheet 2-4 in the images where it says "row used: x". How could that be added? – Chris Jan 02 '20 at 16:40
Then you'll have to adjust the code within the for each sht loop to include that. It now shows you how to get the last used row =) – JvdV Jan 02 '20 at 16:55

score 0 · Answer 2 · answered Jan 06 '20 at 16:32

You could use a UDF.

Public Function RowCount(Optional ShtRef As Range) As Long

    Application.Volatile

    Dim Ref As Range

    If ShtRef Is Nothing Then
        Set Ref = Application.Caller
    Else
        Set Ref = ShtRef
    End If

    With Ref
        If ShtRef Is Nothing Then
            RowCount = .End(xlUp).Row 'Last row above where formula is called from.
        Else
            RowCount = .Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Row 'All rows on referenced sheet.
        End If
    End With

End Function

Now the formula =RowCount() will return the last row in column A above where you typed the formula, while =RowCount(Sheet2!$A$1) will return the total row count on Sheet2.

Not perfect as it would include your Rows Used when looking at other sheets, so not expecting an accepted answer from it - just for info really.

Excel Row Count Per Sheet

2 Answers2