excel vba - How to make a sheet scroll to the last row with data once a button is clicked -

i have looked through forum, google, , wasn't able find answer i'm trying do.

one of users [here][1] helped me code copy data in specific row (upon clicking button) last row of table found further down in same sheet.

however, i'd sheet scroll down last non-empty row of table instead of scrolling manually. understand can accomplished through combination of ctrl+shift+"down arrow". however; excel users not computers, trying make simple possible them.

is there vba code can job?

thanks

so far, using erin's code, takes me last row of spread sheet, instead of last non-blank row. because column has formulas in cells, way down last cell. however, column cells formulas set give blank unless there's data entered in adjacent cells in column e.

here's code using, pasted in module.

option explicit sub copyrow() dim ws worksheet dim lrow long  ' define worksheet work on, i.e. replace sheet1 name of  sheet set ws = activeworkbook.sheets("1. clients details")  ' determine last row content in column e , add 1 lrow = ws.cells(rows.count, "e").end(xlup).row + 1  ' copy cells ranges ws.range("e3:g3").copy ws.range("e" & lrow) ws.[e1].select  ' combine h3, i3, j3, k3 , l3 , copy column e, next empty row  ws.range("h" & lrow) = ws.[h3] & " " & ws.[i3] & " " & ws.[j3] & ", " & ws. [k3] & " " & ws.[l3]  ' copy other cells ranges ws.range("m3:q3").copy ws.range("m" & lrow) ws.[m1].select  ' combine h3 & i3 ws.range("aa" & lrow) = ws.[h3] & " " & ws.[i3]  ' combine j3, k3 & l3 ws.range("ab" & lrow) = ws.[j3] & "       " & ws.[k3] & "       " & ws.[l3]   ' copy q3 column q only, if f3 = "company" if worksheets("1. clients details").range("f3").value = "company" ws.range("q3").copy ws.range("q" & lrow)  end if call scrolltoend end sub  sub scrolltoend() dim lastrow long dim numrows long numrows = range("e1:e1000").rows.count lastrow = range("e1:e1000").columns(5).rows(numrows).row  activesheet.range("e" & lastrow).activate end sub 

dim lastrow long dim numrows long numrows = range ("tablename").rows.count lastrow = range ("tablename").columns (1).rows(numrows).row  activesheet.range("a" & lastrow).activate 

i can't test right now, believe work. if know offset table, can numrows + offset (mine in a1, add 1 header - numrows data rows) row number .activate. :-)

or reach same row ctrl+shift+"down arrow", regardless of table:

with activesheet    .range("a" & .usedrange.rows(.usedrange.rows.count).row).activate end 

edited: thinking ctrl+end in above code. simulate ctrl+"down arrow" (adding shift selects in path...), use:

range("a1").end(xldown).activate 

you paste @ end of sub since 1 line, or keep own little sub if wanting call button-click. if column e want selected, replace "a1" "e1".

this assume there no blank cells in column e between "e1" , last non-blank row. otherwise, need use same logic in copyrow sub find last non-blank row:

activesheet.cells(rows.count, "e").end(xlup).activate