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

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

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

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


--
Don Guillett
SalesAid Software

"Bird" wrote in message
...

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

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

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
------------------------------------------------------------------------
Bird's Profile: http://www.excelforum.com/member.php...o&userid=24469
View this thread: http://www.excelforum.com/showthread...hreadid=500390

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

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
------------------------------------------------------------------------
Bird's Profile: http://www.excelforum.com/member.php...o&userid=24469
View this thread: http://www.excelforum.com/showthread...hreadid=500390

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

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

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

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

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

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