Avatar of ArisaAnsar
ArisaAnsar
Flag for United States of America asked on

Count of Rows using VBA

Hello.
I have a simple macro where I just need to count the number of rows on two sheets and list it on the Receipts tab.

I have the following code but I cannot seem to get it to work.   Any help in identifying where I am going wrong will be appreciated.

Option Explicit
Dim wk As Workbook
Dim ws As Worksheet
Dim Lrow As Long
Dim rng As Range
Dim i As Long

Sub Env()

    Set wk = ThisWorkbook
    Set ws = wk.Worksheets("Receipts")
   
   
    Lrow = .Range("A" & .Rows.Count).End(xlUp).Row
   
   
    With ws
        .Range("B2").Formula = "=CountA('0-29'!A2&Lrow)"
        .Range("B3").Formula = "=CountA('30-60'!A2&Lrow)"
    End With
   
    MsgBox Completed
   

End Sub
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Roy Cox

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ardhendu Sarangi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ArisaAnsar

ASKER
Thank you.  This worked.
Roy Cox

If the data on the two sheets is in a table format then try this. Adjust the sheets to match yours and the destination cell

Sheet1.Range("B2").Value = Sheet2.Range("A1").CurrentRegion.Rows.Count + Sheet3.Range("A1").CurrentRegion.Rows.Count

Open in new window

ArisaAnsar

ASKER
Hi. I just realized it is counting the heading row.  How would I tweak it to exclude the heading?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ardhendu Sarangi

Hi Arisa,

Here is the modified version where it doesn't count the Header Row -

Option Explicit
Dim wk As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim Lrow As Long
Dim rng As Range
Dim i As Long
Sub Env()

    Set wk = ThisWorkbook
    Set ws1 = wk.Worksheets("Receipts")
    Set ws2 = wk.Worksheets("0-29")
    Set ws3 = wk.Worksheets("30-60")
       
    With ws2
        LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
        ws1.Range("B2").Value = LastRow1
    End With
   
    With ws3
        LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
        ws1.Range("B3").Value = LastRow2
    End With
   
    MsgBox ("Completed")
   

End Sub
Roy Cox

Sheet1.Range("B2").Value = Sheet2.Range("A1").CurrentRegion.Rows.Count - 1 + Sheet3.Range("A1").CurrentRegion.Rows.Count - 1

Open in new window