Help with Countrows functionality within a Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I recently, found that I needed a mechanism to count the number of rows that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I wanted.

I decided that I would insert the functionality into a macro, which is used
on a quarterly basis that basically, merges three sheets, then creates
individual sheets for each company and finally undertakes Page formatting.

Within the format macro I inserted the following:- (thank you to Dave Ramage)

Sub DoStuff()
Dim wks as Worksheet
Dim lLastRow as Long

For Each wks in Activeworkbook.Worksheets
lLastRow = wks.Range("A1").End(xlDown).Row
wks.PageSetup.PrintArea = "$A$1:$K$" & lLastRow
wks.Cells(lLastRow + 1, 1).Formula = "TOTAL"
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTROWS(A:A) – 2"
‘the –2 at the end is to discard one header line, and the countrows line
Next wks
End Sub

Unfortunately when I run it, I get a ‘Run-time error 1004 –
Application-defined or object-defined error’.

I have had a look at it, and word TOTAL is inserted in the last row in
Column A for the very first sheet only. It then gives the above error. The
line it seems to complain about is wks.Cells(lLastRow + 1, 2).Formula =
"=COUNTROWS(A:A) – 2"

Any suggestions would be most welcome

Regards

Pank
 
Assuming that you have a function called COUNTROWS, try this#

ub DoStuff()
Dim wks As Worksheet
Dim lLastRow As Long

For Each wks In ActiveWorkbook.Worksheets
lLastRow = wks.Range("A1").End(xlDown).Row
wks.PageSetup.PrintArea = "$A$1:$K$" & lLastRow
wks.Cells(lLastRow + 1, 1).Formula = "TOTAL"
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTROWS(A:A) - 2"
Next wks
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob,

Thanks for the quick response.

Apart from the first line SUB being relaced by UB, I cannot see any
difference. Where do I insert this code? (P.S. what does UB stand for?)

Thanks

Pank
 
Bob,

As always it works as expected.

Many Thanks for you help and quick response.

Regards

Pank
 
Pank,

For your info, it was the hyphen. Somehow your dash wasn't the correct one,
no idea why.
 
Back
Top