excel - VBA: Macro to loop through rows and Autofill -

excel - VBA: Macro to loop through rows and Autofill -

edit: if instead wanted autofill these cells, next code work?

sub bc_edit() ' define width , height of table dim datasetwidth, datasetheight integer ' find values width , height of table datasetwidth = range("a1").end(xltoright).column datasetheight = range("a1").end(xldown).row ' loop on each column x = 1 datasetwidth set sourcerange = cells(2,x) set fillrange = range(cells(3, x), cells(datasetheight, x)) sourcerange.autofill destination:=fillrange next end sub

i'm working couple of extremely big datasets - each approximately 3000 rows 4000 columns. while excel may not best tool job, have built important amount of infrastructure around info , cannot move different framework. i'm using excel 2007.

in particular worksheet, when seek autofill using formula have inputted entire sec column b (3000 x 1) via re-create , paste of column remaining 3000 3998 selection, or part of selection, excel gives me memory/resources error. instead loop through each row , autofill across columns. (in other words, i'd utilize a2 autofill a3:a4000, b2 autofill b3:b4000, , on.) perhaps help memory issue. how go writing macro accomplish this?

i'd appreciate advice on issue, , perhaps help appropriate vba code, if possible.

here pretty basic illustration of macro set columns below 2 formula of column 2. best have attached button or similar rather running every time open sheet.

sub button_click() ' define width , height of table dim datasetwidth, datasetheight integer ' find values width , height of table datasetwidth = range("a1").end(xltoright).column datasetheight = range("a1").end(xldown).row ' loop on each column x = 1 datasetwidth ' row 3 height of data, set formula of cells ' formula contained in row 2 of column range(cells(3, x), cells(datasetheight, x)).formula = cells(2, x).formula next end sub

excel vba