Count of Rows using VBA

ArisaAnsar
ArisaAnsar used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr. Project Manager
Commented:
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

Author

Commented:
Thank you.  This worked.
Roy CoxGroup Finance Manager

Commented:
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

Ensure you’re charging the right price for your IT

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

Author

Commented:
Hi. I just realized it is counting the heading row.  How would I tweak it to exclude the heading?
Ardhendu SarangiSr. Project Manager

Commented:
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 Manager

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

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial