Thread: [SOLVED] Help with Countrows functionality within a Macro

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)


"Pank" <Pank@discussions.microsoft.com> wrote in message
news:48E2C597-A4C7-4EF4-9395-8E0C64AA9AB7@microsoft.com...
> 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
>

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 Phillips" wrote:

> 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)
>
>
> "Pank" <Pank@discussions.microsoft.com> wrote in message
> news:48E2C597-A4C7-4EF4-9395-8E0C64AA9AB7@microsoft.com...
> > 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
> >
>
>
>

My reply dropped the S. Put it back in :-).

Did you try it?

--
HTH

Bob Phillips

"Pank" <Pank@discussions.microsoft.com> wrote in message
news:875CFA97-0A97-416E-A1CA-A70D78C4DE05@microsoft.com...
> 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 Phillips" wrote:
>
> > 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)
> >
> >
> > "Pank" <Pank@discussions.microsoft.com> wrote in message
> > news:48E2C597-A4C7-4EF4-9395-8E0C64AA9AB7@microsoft.com...
> > > 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
> > >
> >
> >
> >

Bob,

As always it works as expected.

Many Thanks for you help and quick response.

Regards

Pank



"Bob Phillips" wrote:

> My reply dropped the S. Put it back in :-).
>
> Did you try it?
>
> --
> HTH
>
> Bob Phillips
>
> "Pank" <Pank@discussions.microsoft.com> wrote in message
> news:875CFA97-0A97-416E-A1CA-A70D78C4DE05@microsoft.com...
> > 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 Phillips" wrote:
> >
> > > 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)
> > >
> > >
> > > "Pank" <Pank@discussions.microsoft.com> wrote in message
> > > news:48E2C597-A4C7-4EF4-9395-8E0C64AA9AB7@microsoft.com...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>

Pank,

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

--
HTH

Bob Phillips

"Pank" <Pank@discussions.microsoft.com> wrote in message
news:B5340F41-7878-4E53-8AEF-0DB3F50A177D@microsoft.com...
> Bob,
>
> As always it works as expected.
>
> Many Thanks for you help and quick response.
>
> Regards
>
> Pank
>
>
>
> "Bob Phillips" wrote:
>
> > My reply dropped the S. Put it back in :-).
> >
> > Did you try it?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Pank" <Pank@discussions.microsoft.com> wrote in message
> > news:875CFA97-0A97-416E-A1CA-A70D78C4DE05@microsoft.com...
> > > 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 Phillips" wrote:
> > >
> > > > 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)
> > > >
> > > >
> > > > "Pank" <Pank@discussions.microsoft.com> wrote in message
> > > > news:48E2C597-A4C7-4EF4-9395-8E0C64AA9AB7@microsoft.com...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >