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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.CountASKER
Hi. I just realized it is counting the heading row. How would I tweak it to exclude the heading?
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
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
Sheet1.Range("B2").Value = Sheet2.Range("A1").CurrentRegion.Rows.Count - 1 + Sheet3.Range("A1").CurrentRegion.Rows.Count - 1
ASKER