Help with "Selection.End(xlUp).Row"

[Bird]

What could cause this code to malfunction on the
“dLastZRowPopulated = Selection.End(xlUp).Row” in:

Sub Macro1()
Dim dLastZRowPopulated As Double
Application.Goto Reference:="R10101C26" ‘Z(101:105)
Range("Z10101").Select
dLastZRowPopulated = Selection.End(xlUp).Row
End Sub

This code presented in its simplest form, and results in dLastZRowPopulated =100, however Z(101:Z105) are populated. Any insights as to how Z(101:Z105) are ignored?

By the way it happens on the spreadsheet as well. When in Z10101, hitting [Ctrl] + [Up arrow] takes me to Z100 !?!?

[Don Guillett]

use
dlrp=cells(rows.count,"z").end(xlup).row


--
Don Guillett
SalesAid Software
[email protected]
"Bird" <[email protected]> wrote in message
news:[email protected]...
>
> What could cause this code to malfunction on the
> “dLastZRowPopulated = Selection.End(xlUp).Row” in:
>
> Sub Macro1()
> Dim dLastZRowPopulated As Double
> Application.Goto Reference:="R10101C26" ‘Z(101:105)
> Range("Z10101").Select
> dLastZRowPopulated = Selection.End(xlUp).Row
> End Sub
>
> This code presented in its simplest form, and results in
> dLastZRowPopulated =100, however Z(101:Z105) are populated. Any
> insights as to how Z(101:Z105) are ignored?
>
> By the way it happens on the spreadsheet as well. When in Z10101,
> hitting [Ctrl] + [Up arrow] takes me to Z100 !?!?
>
>
> --
> Bird
> ------------------------------------------------------------------------
> Bird's Profile:
> http://www.excelforum.com/member.php...o&userid=24469
> View this thread: http://www.excelforum.com/showthread...hreadid=500390
>

[Bird]

Thanks Don

after "dLastZRowPopulated = Cells(Rows.Count, "z").End(xlUp).Row ",
dLastZRowPopulated =10101has so it went nowhere! The real sticker is when in Z10101, on spreedsheet, hitting [Ctrl] + [Up arrow] takes me to Z100, sounds like environment or format getting in the way?

[Bird]

Thanks Don

after "dLastZRowPopulated = Cells(Rows.Count, "z").End(xlUp).Row ",
dLastZRowPopulated =10101has so it went nowhere! The real sticker is when in Z10101, on spreedsheet, hitting [Ctrl] + [Up arrow] takes me to Z100, sounds like environment or format getting in the way?

[Dave Peterson]

Any chance that Z10101 to Z101 have something in them--even formulas that
evaluate to "".

In fact, if you had formulas that evaluated to "" in those cells, then converted
them to values (edit|copy, edit|paste special|Values), then those cells aren't
empty.

Try
=counta(Z101:Z10101)

What do you get back?

Bird wrote:
>
> What could cause this code to malfunction on the
> “dLastZRowPopulated = Selection.End(xlUp).Row” in:
>
> Sub Macro1()
> Dim dLastZRowPopulated As Double
> Application.Goto Reference:="R10101C26" ‘Z(101:105)
> Range("Z10101").Select
> dLastZRowPopulated = Selection.End(xlUp).Row
> End Sub
>
> This code presented in its simplest form, and results in
> dLastZRowPopulated =100, however Z(101:Z105) are populated. Any
> insights as to how Z(101:Z105) are ignored?
>
> By the way it happens on the spreadsheet as well. When in Z10101,
> hitting [Ctrl] + [Up arrow] takes me to Z100 !?!?
>
> --
> Bird
> ------------------------------------------------------------------------
> Bird's Profile: http://www.excelforum.com/member.php...o&userid=24469
> View this thread: http://www.excelforum.com/showthread...hreadid=500390

--

Dave Peterson

[Bird]

YES,YES,YES! Dave you hit it on the head. Formula in Z101:Z10101 is “=IF(COUNTA(B101:G101)=0,"",COUNTA(B101:G101))”. I then copy M:M and PasteSpecial w/ values, in M:M, to avoid hitting on the formulas. What is the way to “Selection.End(xlUp).Row” and hitting a formula result other than ””?

[Dave Peterson]

Better to clear up that detritus.

Select all your cells (or just column Z if you want to limit it).

Edit|replace
what: (leave blank)
with: $$$$$
replace all

Followed by:
edit|Replace
what: $$$$$
with: (leave blank)
replace all

All those cells that evaluated to "" and were converted to values will be
cleaned up.

And .end(xlup) will work as it should.

Bird wrote:
>
> YES,YES,YES! Dave you hit it on the head. Formula in Z101:Z10101 is
> “=IF(COUNTA(B101:G101)=0,"",COUNTA(B101:G101))”. I then copy M:M and
> PasteSpecial w/ values, in M:M, to avoid hitting on the formulas. What
> is the way to “Selection.End(xlUp).Row” and hitting a formula result
> other than ””?
>
> --
> Bird
> ------------------------------------------------------------------------
> Bird's Profile: http://www.excelforum.com/member.php...o&userid=24469
> View this thread: http://www.excelforum.com/showthread...hreadid=500390

--

Dave Peterson