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
ArisaAnsarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ardhendu SarangiSr. Project ManagerCommented:
The error you are getting is because you are trying to use VBA Variable in the CountA formula.

here is a modified version of the macro.. let me know if this what you were looking for?


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
        ws1.Range("B2").Value = LastRow1
    End With
   
    With ws3
        LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        ws1.Range("B3").Value = LastRow2
    End With
   
    MsgBox ("Completed")
   

End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ArisaAnsarAuthor Commented:
Thank you.  This worked.
Roy CoxGroup Finance ManagerCommented:
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ArisaAnsarAuthor Commented:
Hi. I just realized it is counting the heading row.  How would I tweak it to exclude the heading?
Ardhendu SarangiSr. Project ManagerCommented:
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 CoxGroup Finance ManagerCommented:
Sheet1.Range("B2").Value = Sheet2.Range("A1").CurrentRegion.Rows.Count - 1 + Sheet3.Range("A1").CurrentRegion.Rows.Count - 1

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.