finding next empty row from bottom up!!
hi, I need to find the next empty row in my data range, but it has several empty rows throughout it. I need to be able to measure from the bottom of a worksheet upwards, on column B and then select the next empty row any ideas? -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076 View this thread: http://www.excelforum.com/showthread...hreadid=481003 |
finding next empty row from bottom up!!
Use this code:Sub lastrow() Dim rRng As Range Set rRng = Range("A1:A" & Range("A" Rows.Count).End(xlUp).Row) With rRng.Offset(rRng.Count, 0).Resize(1, 1).Select End With End Sub places cursor in cell A99999 where 99999=the first row below the las entry in column -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php...fo&userid=1510 View this thread: http://www.excelforum.com/showthread.php?threadid=48100 |
finding next empty row from bottom up!!
Use this code:Sub lastrow() Dim rRng As Range Set rRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) With rRng.Offset(rRng.Count, 0).Resize(1, 1).Select End With End Sub places cursor in cell A99999 where 99999=the first row below the last entry in column A -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=481003 |
finding next empty row from bottom up!!
Part of your solution will probably need this code: Find the very last used cell in a Column: Sub LastCellInColumn() Range("A65536").End(xlup).Select End Sub Since you know "B" is the column with the last data, change the code to read: Range("B65536").End(x1up).Select Perhaps one of the more experienced members of this group can show us how to use this... -- Campbellj4 ------------------------------------------------------------------------ Campbellj4's Profile: http://www.excelforum.com/member.php...o&userid=28506 View this thread: http://www.excelforum.com/showthread...hreadid=481003 |
finding next empty row from bottom up!!
Part of your solution will probably need this code: Find the very last used cell in a Column: Sub LastCellInColumn() Range("A65536").End(xlup).Select End Sub Since you know "B" is the column with the last data, change the code to read: Range("B65536").End(x1up).Select Perhaps one of the more experienced members of this group can show us how to use this... -- Campbellj4 ------------------------------------------------------------------------ Campbellj4's Profile: http://www.excelforum.com/member.php...o&userid=28506 View this thread: http://www.excelforum.com/showthread...hreadid=481003 |
finding next empty row from bottom up!!
swatsp0p Wrote: Use this code:Sub lastrow() Dim rRng As Range Set rRng = Range("A1:A" & Range("A" Rows.Count).End(xlUp).Row) With rRng.Offset(rRng.Count, 0).Resize(1, 1).Select End With End Sub places cursor in cell A99999 where 99999=the first row below the las entry in column A Of course for Column B, simply change the reference in the above cod from 'A' to 'B' each place it occurs. Also, I forgot to mention that if you want to skip 1 or more rows belo the last entry, adjust the "Offset(rRng.Count, 0)" by changing the zer to as many rows as you want to skip. e.g to leave 2 blank rows, chang the zero to a 2. If the last active row was 100, the cursor would b placed in 103 (skipping 101 & 102). You can assign this code to a button or simply run it by pressin Alt+F8 and selecting "lastrow" from the list. Does this work for you -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php...fo&userid=1510 View this thread: http://www.excelforum.com/showthread.php?threadid=48100 |
All times are GMT +1. The time now is 01:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com