We help IT Professionals succeed at work.

Count of Rows using VBA

70 Views
Last Modified: 2018-12-05
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

Sr. Project Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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

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

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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

Open in new window

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.