Better way of finding last row on sheet
Hi,
Can anyone improve on this formula to find the last row of a sheet:- Sheet1.Range("A65536").End(xlUp).Row This keeps finding row 26 as the last row when I only have 3 rows of data. Any ideas? Andi |
Better way of finding last row on sheet
Andi,
Did you check cell A26 to see if it had a space or some non-printing character in it? Regards, Jim Cone San Francisco, USA "Andibevan" wrote in message ... Hi, Can anyone improve on this formula to find the last row of a sheet:- Sheet1.Range("A65536").End(xlUp).Row This keeps finding row 26 as the last row when I only have 3 rows of data. Any ideas? Andi |
Better way of finding last row on sheet
Hi Andibevan,
I guess that the last cell is really in 26th row. This should select it (the same as Ctrl+End) sub selectlastcell() Cells.SpecialCells(xlCellTypeLastCell).select end sub Save your work and use this to recover your really last cell: (this will reset the undo operations memory!) sub recoverlastcell() ActiveSheet.UsedRange end sub |
Better way of finding last row on sheet
"Andibevan" wrote in message ... Hi, Can anyone improve on this formula to find the last row of a sheet:- Sheet1.Range("A65536").End(xlUp).Row This keeps finding row 26 as the last row when I only have 3 rows of data. Any ideas? Andi sounds likes cell(A26) an "invisible" space/etc character ?? |
Better way of finding last row on sheet
Do you have formulas in cells 4 thru 26? This would cause your problem. You could also do something like Code: -------------------- Range("A1").Select While Activecell.Value < "" Selection.Offset(1,0).Select Wend -------------------- HTH -- tkstock ------------------------------------------------------------------------ tkstock's Profile: http://www.excelforum.com/member.php...o&userid=14443 View this thread: http://www.excelforum.com/showthread...hreadid=379635 |
Better way of finding last row on sheet
"Andibevan" wrote in message ... Hi, Can anyone improve on this formula to find the last row of a sheet:- Sheet1.Range("A65536").End(xlUp).Row This keeps finding row 26 as the last row when I only have 3 rows of data. I very much doubt it, this technique doesn't suffer the last used cell problem that say Ctlr-End suffers. You must have something in A26. Try ?LEN(Range("A26")) in the immediate window |
Better way of finding last row on sheet
Thank-you all for your very helpful comments - I was missing the obvious (as
usual). There was some hidden text in row 26 - now works fine Thanks all "Bob Phillips" wrote in message ... "Andibevan" wrote in message ... Hi, Can anyone improve on this formula to find the last row of a sheet:- Sheet1.Range("A65536").End(xlUp).Row This keeps finding row 26 as the last row when I only have 3 rows of data. I very much doubt it, this technique doesn't suffer the last used cell problem that say Ctlr-End suffers. You must have something in A26. Try ?LEN(Range("A26")) in the immediate window |
Better way of finding last row on sheet
The first question is, is there anything in cell a26? This procedure will
stop at anything Ashman "Andibevan" wrote: Hi, Can anyone improve on this formula to find the last row of a sheet:- Sheet1.Range("A65536").End(xlUp).Row This keeps finding row 26 as the last row when I only have 3 rows of data. Any ideas? Andi |
All times are GMT +1. The time now is 02:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com