Excel vba: combine multiple files in one sheet -

i have 100+ files in 1 folder. each file has 3 lists, 1 list data. need take data each file , combine in single file on 1 list. wrote sub it, i'm not sure how go around selecting range needed (it varies file file) - in same way on keyboard ctrl + shift + left arrow + down arrow. , how should go around pasting in result workbook @ first free line after data pasted before?

sub combine()         application.screenupdating = false         application.displayalerts = false         application.calculation = xlcalculationmanual          dim excelapp object         set excelapp = createobject("excel.application")         excelapp.visible = false         excelapp.screenupdating = false         excelapp.displayalerts = false         excelapp.enableevents = false          '**variables**         dim folderpath string         folderpath = "y:\plan_graphs\final\mich_alco_test\files\"          'count files         dim totalfiles long         totalfiles = 0         dim filetitle string         filetitle = dir(folderpath & "*.xl??")         while filetitle <> ""             totalfiles = totalfiles + 1             filetitle = dir()         loop          'opening files         dim resultworkbook workbook         dim dataworkbook workbook         set resultworkbook = excelapp.application.workbooks.open("y:\plan_graphs\final\mich_alco_test\result.xlsx")           filetitle = dir(folderpath & "*.xl??")          'for each file         while filetitle <> ""             set dataworkbook = excelapp.application.workbooks.open(folderpath & filetitle)             dataworkbook.worksheets("list1").range("a1").select             dataworkbook.worksheets("list1").selection.currentregion.select                `resultworkbook.range              filetitle = dir()          loop      excelapp.quit     set excelapp = nothing end sub 

i may have misunderstood question , unfortunately cannot make comment. if i've grasped question wrong, i'll delete.

but i'm not sure how go around selecting range needed

this suggests have dynamic amount of data , want use range grab selections.

supposing know column location of said data located (in case list starts @ b2 , don't know ends. can use range dynamically select data:

dim rcell range dim rng range  set rng = activesheet.range("b2", range("b2").end(xldown))  each rcell in rng.cells     debug.print rcell.value next rcell  end sub 

first define range variable , assign range starting @ b2 , using .end(xldown) can select range ending @ final entry.

for further reading on .end() see here.

hope helps.