Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Icon for "Wrap" and for "Centre Across Selection" | Excel Worksheet Functions | |||
inalRow = Range("A65536").End(xlUp).Row | Excel Programming | |||
S1.Range("D65536").End(xlUp).Select | Excel Programming | |||
Question about "End(xlUp)" property | Excel Programming | |||
Question about "End(xlUp)" property | Excel Programming |