What does this do?

In the code, Rows.Count returns the number of rows in the
worksheet, or 65536. Therefore, the

..Cells(Rows.Count,2)

refers to the last cell in column 2. Then, the .End(xlUp) causes
Excel to scan upwards until a non-empty cell is found. The .Row
property returns the row number of that last cell.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"davegb" <davegb@safebrowse.com> wrote in message
news:1124723331.791232.39630@o13g2000cwo.googlegroups.com...
>I got some help here writing some code. I'm trying to do
>something very
> similar to what this line does, but I don't understand this
> line.
>
> lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count,
> 2) _
> .End(xlUp).Row
>
> I know it defines the final row in the named worksheet. What I
> don't
> understand is what the .Count does, or why the xlUp is not a
> xlDown.
> Can someone explain this line for me?
> Thanks!
>

the rows.ccount returns the number of rows in the spreadsheet (65,536). The
..end(xlUp) travels up from this row to the first non-blank cell. Personally
(and this is a personal preference) I would write that line like this...

lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
..End(xlUp).Row

because in my opionion
Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
--
HTH...

Jim Thomlinson


"davegb" wrote:

> I got some help here writing some code. I'm trying to do something very
> similar to what this line does, but I don't understand this line.
>
> lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> .End(xlUp).Row
>
> I know it defines the final row in the named worksheet. What I don't
> understand is what the .Count does, or why the xlUp is not a xlDown.
> Can someone explain this line for me?
> Thanks!
>
>

While the need is dissipating, in xl5/95, you choice would error. That is
why most use Rows.count which would work in all situations.

--
Regards,
Tom Ogilvy

"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> the rows.ccount returns the number of rows in the spreadsheet (65,536).
The
> .end(xlUp) travels up from this row to the first non-blank cell.
Personally
> (and this is a personal preference) I would write that line like this...
>
> lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> .End(xlUp).Row
>
> because in my opionion
> Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> --
> HTH...
>
> Jim Thomlinson
>
>
> "davegb" wrote:
>
> > I got some help here writing some code. I'm trying to do something very
> > similar to what this line does, but I don't understand this line.
> >
> > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > .End(xlUp).Row
> >
> > I know it defines the final row in the named worksheet. What I don't
> > understand is what the .Count does, or why the xlUp is not a xlDown.
> > Can someone explain this line for me?
> > Thanks!
> >
> >

I got some help here writing some code. I'm trying to do something very
similar to what this line does, but I don't understand this line.

lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
.End(xlUp).Row

I know it defines the final row in the named worksheet. What I don't
understand is what the .Count does, or why the xlUp is not a xlDown.
Can someone explain this line for me?
Thanks!

And you can use

Cells(Rows.Count,"B")

as you now know :-)

Bob


"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
news:CA23903E-4352-44DF-AE45-5D19E0A3912A@microsoft.com...
> Thanks Tom. I didn't know that. My programming started after that
(obviously).
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tom Ogilvy" wrote:
>
> > While the need is dissipating, in xl5/95, you choice would error. That
is
> > why most use Rows.count which would work in all situations.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > > the rows.ccount returns the number of rows in the spreadsheet
(65,536).
> > The
> > > .end(xlUp) travels up from this row to the first non-blank cell.
> > Personally
> > > (and this is a personal preference) I would write that line like
this...
> > >
> > > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > > .End(xlUp).Row
> > >
> > > because in my opionion
> > > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "davegb" wrote:
> > >
> > > > I got some help here writing some code. I'm trying to do something
very
> > > > similar to what this line does, but I don't understand this line.
> > > >
> > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > > .End(xlUp).Row
> > > >
> > > > I know it defines the final row in the named worksheet. What I don't
> > > > understand is what the .Count does, or why the xlUp is not a xlDown.
> > > > Can someone explain this line for me?
> > > > Thanks!
> > > >
> > > >
> >
> >
> >

I am off to get some coffee. This is more Monday than I am prepared for... :-)
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

> And you can use
>
> Cells(Rows.Count,"B")
>
> as you now know :-)
>
> Bob
>
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:CA23903E-4352-44DF-AE45-5D19E0A3912A@microsoft.com...
> > Thanks Tom. I didn't know that. My programming started after that
> (obviously).
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > While the need is dissipating, in xl5/95, you choice would error. That
> is
> > > why most use Rows.count which would work in all situations.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > > news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > > > the rows.ccount returns the number of rows in the spreadsheet
> (65,536).
> > > The
> > > > .end(xlUp) travels up from this row to the first non-blank cell.
> > > Personally
> > > > (and this is a personal preference) I would write that line like
> this...
> > > >
> > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > > > .End(xlUp).Row
> > > >
> > > > because in my opionion
> > > > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "davegb" wrote:
> > > >
> > > > > I got some help here writing some code. I'm trying to do something
> very
> > > > > similar to what this line does, but I don't understand this line.
> > > > >
> > > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > > > .End(xlUp).Row
> > > > >
> > > > > I know it defines the final row in the named worksheet. What I don't
> > > > > understand is what the .Count does, or why the xlUp is not a xlDown.
> > > > > Can someone explain this line for me?
> > > > > Thanks!
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Chip Pearson wrote:
> > why count up from the bottom instead of
> > down from the data itself? Is this to handle the potential for
> > blank
> > cells in that column?
>
> If the data contains embedded blank cells, the End(xlDown) method
> will not take you to the last cell. It will take you to the cell
> above the first blank cell. Going from the bottom up will always
> take you to the last cell with data.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com

Thanks!
>
>
>
>
> "davegb" <davegb@safebrowse.com> wrote in message
> news:1124726972.523521.41100@f14g2000cwb.googlegroups.com...
> >
> > Tom Ogilvy wrote:
> >> While the need is dissipating, in xl5/95, you choice would
> >> error. That is
> >> why most use Rows.count which would work in all situations.
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in
> >> message
> >> news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> >> > the rows.ccount returns the number of rows in the
> >> > spreadsheet (65,536).
> >> The
> >> > .end(xlUp) travels up from this row to the first non-blank
> >> > cell.
> >> Personally
> >> > (and this is a personal preference) I would write that line
> >> > like this...
> >> >
> >> > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536")
> >> > _
> >> > .End(xlUp).Row
> >> >
> >> > because in my opionion
> >> > Range("B65536") is far more clear to read than
> >> > Cells(Rows.Count, 2)
> >> > --
> >> > HTH...
> >> >
> >> > Jim Thomlinson
> >> >
> >> >
> >> > "davegb" wrote:
> >> >
> >> > > I got some help here writing some code. I'm trying to do
> >> > > something very
> >> > > similar to what this line does, but I don't understand
> >> > > this line.
> >> > >
> >> > > lEndRow = wbkNewHdr.Sheets("Macro
> >> > > Records").Cells(Rows.Count, 2) _
> >> > > .End(xlUp).Row
> >> > >
> >> > > I know it defines the final row in the named worksheet.
> >> > > What I don't
> >> > > understand is what the .Count does, or why the xlUp is not
> >> > > a xlDown.
> >> > > Can someone explain this line for me?
> >> > > Thanks!
> >> > >
> >> > >
> >
> > thanks to everyone for your prompt and helpful replies! Your
> > explanations beg the question, why count up from the bottom
> > instead of
> > down from the data itself? Is this to handle the potential for
> > blank
> > cells in that column? If so, then if there were blanks in the
> > bottom
> > rows in this column, you'd get a miscount anyway. Is there some
> > other
> > reason for counting up from the bottom of the sheet?
> >

Yep, 2^14

--

HTH

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


"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
news:7A289FE4-A970-4713-B9DE-A058829A3EAD@microsoft.com...
> Out of shear curiosity how many rows does 95 have? It is around 16k isn't
it?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tom Ogilvy" wrote:
>
> > While the need is dissipating, in xl5/95, you choice would error. That
is
> > why most use Rows.count which would work in all situations.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > > the rows.ccount returns the number of rows in the spreadsheet
(65,536).
> > The
> > > .end(xlUp) travels up from this row to the first non-blank cell.
> > Personally
> > > (and this is a personal preference) I would write that line like
this...
> > >
> > > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > > .End(xlUp).Row
> > >
> > > because in my opionion
> > > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "davegb" wrote:
> > >
> > > > I got some help here writing some code. I'm trying to do something
very
> > > > similar to what this line does, but I don't understand this line.
> > > >
> > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > > .End(xlUp).Row
> > > >
> > > > I know it defines the final row in the named worksheet. What I don't
> > > > understand is what the .Count does, or why the xlUp is not a xlDown.
> > > > Can someone explain this line for me?
> > > > Thanks!
> > > >
> > > >
> >
> >
> >

It is indeed 16K rows.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
news:7A289FE4-A970-4713-B9DE-A058829A3EAD@microsoft.com...
> Out of shear curiosity how many rows does 95 have? It is around
> 16k isn't it?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tom Ogilvy" wrote:
>
>> While the need is dissipating, in xl5/95, you choice would
>> error. That is
>> why most use Rows.count which would work in all situations.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in
>> message
>> news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
>> > the rows.ccount returns the number of rows in the
>> > spreadsheet (65,536).
>> The
>> > .end(xlUp) travels up from this row to the first non-blank
>> > cell.
>> Personally
>> > (and this is a personal preference) I would write that line
>> > like this...
>> >
>> > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536")
>> > _
>> > .End(xlUp).Row
>> >
>> > because in my opionion
>> > Range("B65536") is far more clear to read than
>> > Cells(Rows.Count, 2)
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "davegb" wrote:
>> >
>> > > I got some help here writing some code. I'm trying to do
>> > > something very
>> > > similar to what this line does, but I don't understand
>> > > this line.
>> > >
>> > > lEndRow = wbkNewHdr.Sheets("Macro
>> > > Records").Cells(Rows.Count, 2) _
>> > > .End(xlUp).Row
>> > >
>> > > I know it defines the final row in the named worksheet.
>> > > What I don't
>> > > understand is what the .Count does, or why the xlUp is not
>> > > a xlDown.
>> > > Can someone explain this line for me?
>> > > Thanks!
>> > >
>> > >
>>
>>
>>

Out of shear curiosity how many rows does 95 have? It is around 16k isn't it?
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

> While the need is dissipating, in xl5/95, you choice would error. That is
> why most use Rows.count which would work in all situations.
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > the rows.ccount returns the number of rows in the spreadsheet (65,536).
> The
> > .end(xlUp) travels up from this row to the first non-blank cell.
> Personally
> > (and this is a personal preference) I would write that line like this...
> >
> > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > .End(xlUp).Row
> >
> > because in my opionion
> > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "davegb" wrote:
> >
> > > I got some help here writing some code. I'm trying to do something very
> > > similar to what this line does, but I don't understand this line.
> > >
> > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > .End(xlUp).Row
> > >
> > > I know it defines the final row in the named worksheet. What I don't
> > > understand is what the .Count does, or why the xlUp is not a xlDown.
> > > Can someone explain this line for me?
> > > Thanks!
> > >
> > >
>
>
>

> why count up from the bottom instead of
> down from the data itself? Is this to handle the potential for
> blank
> cells in that column?

If the data contains embedded blank cells, the End(xlDown) method
will not take you to the last cell. It will take you to the cell
above the first blank cell. Going from the bottom up will always
take you to the last cell with data.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"davegb" <davegb@safebrowse.com> wrote in message
news:1124726972.523521.41100@f14g2000cwb.googlegroups.com...
>
> Tom Ogilvy wrote:
>> While the need is dissipating, in xl5/95, you choice would
>> error. That is
>> why most use Rows.count which would work in all situations.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in
>> message
>> news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
>> > the rows.ccount returns the number of rows in the
>> > spreadsheet (65,536).
>> The
>> > .end(xlUp) travels up from this row to the first non-blank
>> > cell.
>> Personally
>> > (and this is a personal preference) I would write that line
>> > like this...
>> >
>> > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536")
>> > _
>> > .End(xlUp).Row
>> >
>> > because in my opionion
>> > Range("B65536") is far more clear to read than
>> > Cells(Rows.Count, 2)
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "davegb" wrote:
>> >
>> > > I got some help here writing some code. I'm trying to do
>> > > something very
>> > > similar to what this line does, but I don't understand
>> > > this line.
>> > >
>> > > lEndRow = wbkNewHdr.Sheets("Macro
>> > > Records").Cells(Rows.Count, 2) _
>> > > .End(xlUp).Row
>> > >
>> > > I know it defines the final row in the named worksheet.
>> > > What I don't
>> > > understand is what the .Count does, or why the xlUp is not
>> > > a xlDown.
>> > > Can someone explain this line for me?
>> > > Thanks!
>> > >
>> > >
>
> thanks to everyone for your prompt and helpful replies! Your
> explanations beg the question, why count up from the bottom
> instead of
> down from the data itself? Is this to handle the potential for
> blank
> cells in that column? If so, then if there were blanks in the
> bottom
> rows in this column, you'd get a miscount anyway. Is there some
> other
> reason for counting up from the bottom of the sheet?
>

Tom Ogilvy wrote:
> While the need is dissipating, in xl5/95, you choice would error. That is
> why most use Rows.count which would work in all situations.
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > the rows.ccount returns the number of rows in the spreadsheet (65,536).
> The
> > .end(xlUp) travels up from this row to the first non-blank cell.
> Personally
> > (and this is a personal preference) I would write that line like this...
> >
> > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > .End(xlUp).Row
> >
> > because in my opionion
> > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "davegb" wrote:
> >
> > > I got some help here writing some code. I'm trying to do something very
> > > similar to what this line does, but I don't understand this line.
> > >
> > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > .End(xlUp).Row
> > >
> > > I know it defines the final row in the named worksheet. What I don't
> > > understand is what the .Count does, or why the xlUp is not a xlDown.
> > > Can someone explain this line for me?
> > > Thanks!
> > >
> > >

thanks to everyone for your prompt and helpful replies! Your
explanations beg the question, why count up from the bottom instead of
down from the data itself? Is this to handle the potential for blank
cells in that column? If so, then if there were blanks in the bottom
rows in this column, you'd get a miscount anyway. Is there some other
reason for counting up from the bottom of the sheet?

Thanks Tom. I didn't know that. My programming started after that (obviously).
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

> While the need is dissipating, in xl5/95, you choice would error. That is
> why most use Rows.count which would work in all situations.
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > the rows.ccount returns the number of rows in the spreadsheet (65,536).
> The
> > .end(xlUp) travels up from this row to the first non-blank cell.
> Personally
> > (and this is a personal preference) I would write that line like this...
> >
> > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > .End(xlUp).Row
> >
> > because in my opionion
> > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "davegb" wrote:
> >
> > > I got some help here writing some code. I'm trying to do something very
> > > similar to what this line does, but I don't understand this line.
> > >
> > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > .End(xlUp).Row
> > >
> > > I know it defines the final row in the named worksheet. What I don't
> > > understand is what the .Count does, or why the xlUp is not a xlDown.
> > > Can someone explain this line for me?
> > > Thanks!
> > >
> > >
>
>
>

16,784

--
Regards,
Tom Ogilvy

"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
news:7A289FE4-A970-4713-B9DE-A058829A3EAD@microsoft.com...
> Out of shear curiosity how many rows does 95 have? It is around 16k isn't
it?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tom Ogilvy" wrote:
>
> > While the need is dissipating, in xl5/95, you choice would error. That
is
> > why most use Rows.count which would work in all situations.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > > the rows.ccount returns the number of rows in the spreadsheet
(65,536).
> > The
> > > .end(xlUp) travels up from this row to the first non-blank cell.
> > Personally
> > > (and this is a personal preference) I would write that line like
this...
> > >
> > > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > > .End(xlUp).Row
> > >
> > > because in my opionion
> > > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "davegb" wrote:
> > >
> > > > I got some help here writing some code. I'm trying to do something
very
> > > > similar to what this line does, but I don't understand this line.
> > > >
> > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > > .End(xlUp).Row
> > > >
> > > > I know it defines the final row in the named worksheet. What I don't
> > > > understand is what the .Count does, or why the xlUp is not a xlDown.
> > > > Can someone explain this line for me?
> > > > Thanks!
> > > >
> > > >
> >
> >
> >

2^14 = 16,384

So which is it? 16,384 or 16,784? I am guessing 16,384 since it is a power
of 2, but stranger things have happened.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

> 16,784
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:7A289FE4-A970-4713-B9DE-A058829A3EAD@microsoft.com...
> > Out of shear curiosity how many rows does 95 have? It is around 16k isn't
> it?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > While the need is dissipating, in xl5/95, you choice would error. That
> is
> > > why most use Rows.count which would work in all situations.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > > news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > > > the rows.ccount returns the number of rows in the spreadsheet
> (65,536).
> > > The
> > > > .end(xlUp) travels up from this row to the first non-blank cell.
> > > Personally
> > > > (and this is a personal preference) I would write that line like
> this...
> > > >
> > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > > > .End(xlUp).Row
> > > >
> > > > because in my opionion
> > > > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "davegb" wrote:
> > > >
> > > > > I got some help here writing some code. I'm trying to do something
> very
> > > > > similar to what this line does, but I don't understand this line.
> > > > >
> > > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > > > .End(xlUp).Row
> > > > >
> > > > > I know it defines the final row in the named worksheet. What I don't
> > > > > understand is what the .Count does, or why the xlUp is not a xlDown.
> > > > > Can someone explain this line for me?
> > > > > Thanks!
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

16384 not 16784 I think you mean.

--

HTH

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


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23nsHqC0pFHA.2152@TK2MSFTNGP14.phx.gbl...
> 16,784
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:7A289FE4-A970-4713-B9DE-A058829A3EAD@microsoft.com...
> > Out of shear curiosity how many rows does 95 have? It is around 16k
isn't
> it?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > While the need is dissipating, in xl5/95, you choice would error.
That
> is
> > > why most use Rows.count which would work in all situations.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > > news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > > > the rows.ccount returns the number of rows in the spreadsheet
> (65,536).
> > > The
> > > > .end(xlUp) travels up from this row to the first non-blank cell.
> > > Personally
> > > > (and this is a personal preference) I would write that line like
> this...
> > > >
> > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > > > .End(xlUp).Row
> > > >
> > > > because in my opionion
> > > > Range("B65536") is far more clear to read than Cells(Rows.Count, 2)
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "davegb" wrote:
> > > >
> > > > > I got some help here writing some code. I'm trying to do something
> very
> > > > > similar to what this line does, but I don't understand this line.
> > > > >
> > > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _
> > > > > .End(xlUp).Row
> > > > >
> > > > > I know it defines the final row in the named worksheet. What I
don't
> > > > > understand is what the .Count does, or why the xlUp is not a
xlDown.
> > > > > Can someone explain this line for me?
> > > > > Thanks!
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Sorry - typo

you are correct, 16,384

--
Regards,
Tom Ogilvy

"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
news:220AD0A7-72C3-41C7-93E4-CF8195A7450D@microsoft.com...
> 2^14 = 16,384
>
> So which is it? 16,384 or 16,784? I am guessing 16,384 since it is a power
> of 2, but stranger things have happened.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tom Ogilvy" wrote:
>
> > 16,784
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > news:7A289FE4-A970-4713-B9DE-A058829A3EAD@microsoft.com...
> > > Out of shear curiosity how many rows does 95 have? It is around 16k
isn't
> > it?
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > While the need is dissipating, in xl5/95, you choice would error.
That
> > is
> > > > why most use Rows.count which would work in all situations.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > > > news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > > > > the rows.ccount returns the number of rows in the spreadsheet
> > (65,536).
> > > > The
> > > > > .end(xlUp) travels up from this row to the first non-blank cell.
> > > > Personally
> > > > > (and this is a personal preference) I would write that line like
> > this...
> > > > >
> > > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > > > > .End(xlUp).Row
> > > > >
> > > > > because in my opionion
> > > > > Range("B65536") is far more clear to read than Cells(Rows.Count,
2)
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "davegb" wrote:
> > > > >
> > > > > > I got some help here writing some code. I'm trying to do
something
> > very
> > > > > > similar to what this line does, but I don't understand this
line.
> > > > > >
> > > > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2)
_
> > > > > > .End(xlUp).Row
> > > > > >
> > > > > > I know it defines the final row in the named worksheet. What I
don't
> > > > > > understand is what the .Count does, or why the xlUp is not a
xlDown.
> > > > > > Can someone explain this line for me?
> > > > > > Thanks!
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

It must be Monday where you live too... ;-)
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

> Sorry - typo
>
> you are correct, 16,384
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:220AD0A7-72C3-41C7-93E4-CF8195A7450D@microsoft.com...
> > 2^14 = 16,384
> >
> > So which is it? 16,384 or 16,784? I am guessing 16,384 since it is a power
> > of 2, but stranger things have happened.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > 16,784
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > > news:7A289FE4-A970-4713-B9DE-A058829A3EAD@microsoft.com...
> > > > Out of shear curiosity how many rows does 95 have? It is around 16k
> isn't
> > > it?
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > While the need is dissipating, in xl5/95, you choice would error.
> That
> > > is
> > > > > why most use Rows.count which would work in all situations.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > > "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> > > > > news:10B056C0-1A50-4ED8-B707-393D942BFA4F@microsoft.com...
> > > > > > the rows.ccount returns the number of rows in the spreadsheet
> > > (65,536).
> > > > > The
> > > > > > .end(xlUp) travels up from this row to the first non-blank cell.
> > > > > Personally
> > > > > > (and this is a personal preference) I would write that line like
> > > this...
> > > > > >
> > > > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _
> > > > > > .End(xlUp).Row
> > > > > >
> > > > > > because in my opionion
> > > > > > Range("B65536") is far more clear to read than Cells(Rows.Count,
> 2)
> > > > > > --
> > > > > > HTH...
> > > > > >
> > > > > > Jim Thomlinson
> > > > > >
> > > > > >
> > > > > > "davegb" wrote:
> > > > > >
> > > > > > > I got some help here writing some code. I'm trying to do
> something
> > > very
> > > > > > > similar to what this line does, but I don't understand this
> line.
> > > > > > >
> > > > > > > lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2)
> _
> > > > > > > .End(xlUp).Row
> > > > > > >
> > > > > > > I know it defines the final row in the named worksheet. What I
> don't
> > > > > > > understand is what the .Count does, or why the xlUp is not a
> xlDown.
> > > > > > > Can someone explain this line for me?
> > > > > > > Thanks!
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>