What does this do?

> 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?
>

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?
> >