Count No of Rows (Different Sheet) Summary

[markandan26]

Hi All,

I have one Excel which I am having nearly 20 different sheets, now I want in another sheet, in which i should have two columns like “Name of the sheet” and “number of rows”.
Ex: In sheet called Mercury and its having 25 rows, now this should appear like
Sheet Name No. of Rows
Mercury 25
Like this I want for all the 20 sheets information. Hope I am clear with my query. Can someone please help on this?

Thanks,
Mark

 

[My Aswer Is This]

Do not understand "number of rows".
Do you mean number of filled rows?
And what column are we looking in for number of filled rows?

 

[AFPathfinder]

Maybe he means how to link the sheet name in column A into the formula in column B without having to type it in. Otherwise, it's just a simple formula (based on the column that has the data for counting)

=COUNTA(Sheet1!A1:A1048576)

 

[markandan26]

Hi,

Yes, i already told, that i have 20 worksheets. Each worksheets having some data
ex: sheet1 contain 30 recordss
like tis sheet2 30
sheet3 40


etc............

wht i need is....make summary in new sheet

Like

SHeet name	Count of records
Sheet1	30
Sheet2	30
Sheet3	40

<tbody>
</tbody>

 

[AFPathfinder]

Try this:

Option Explicit
Sub ShtRow_Counting()
    Dim oWbk As Workbook
    Dim oSht As Worksheet
    Dim x As Integer
    Set oWbk = ActiveWorkbook
    Worksheets.Add After:=Sheets(Worksheets.Count)
    ActiveSheet.Name = "Row Counts"
    Range("A1").Value = "SHeet name"
    Range("B1").Value = "Count of records"
    x = 2
    For Each oSht In oWbk.Worksheets
        Range("A" & x).Value = oSht.Name
        Range("B" & x).Formula = "=COUNTA('" & oSht.Name & "'!A1:A" & Rows.Count & ")"
        x = x + 1
    Next oSht
    MsgBox "Done."
End Sub

 

[esl]

I have exactly same problem. Is there a formula I could use to solve this?