excel - Optimizing code to fun faster when removing filters/copy data from row of minimum value of filtered data -

excel - Optimizing code to fun faster when removing filters/copy data from row of minimum value of filtered data -

i have info in columns 1 , 2 of sheet 1 (115 rows). these reference values. have info in columns 3 , 4 in activesheet (10000+). these raw info values.

the code have below find closest raw info value reference value taking difference, finding values between 0 , 15 minutes, finding minumum value , copying other info row on location.

i utilize 2 auto filters in process every reference value, happens 115 times. questions follows:

are adding/remove filters slowing downwards search? should through 10,000 info values rather filtering same day first? when find value of minimum value of filtered list, there improve way re-create on info other columns of same row?

i have added timer @ end of code help quantify. hope can help!!

sub updatedtimematcherwithfilters() 'make code fast possible application.screenupdating = false application.calculation = xlcalculationmanual dim minvalue, minrow integer dim searchrange, rng range dim elapsedtime integer starttime = time set searchrange = range("g1:g1697") 'count reference values totalrefval = worksheets("sheet1").cells(rows.count, 5).end(xlup).row 'count raw info values set rng = range("c2:c1000") 'totalrawdataval = activesheet.cells(rows.count, 3).end(xlup).row 'format columns needed columns("g:g").select selection.numberformat = "[h]:mm:ss;@" columns("i:i").select selection.numberformat = "[$-409]m/d/yy h:mm am/pm;@" columns("h:h").select selection.numberformat = "m/d/yy;@" j = 2 totalrefval 'filter date of reference value activesheet.range("$c:$c").autofilter field:=1, operator:=xlfiltervalues, _ criteria2:=array(2, "10/10/2014") 'find difference in times between reference info , raw data, 'put difference value in same row each d in rng.specialcells(xlcelltypevisible) activesheet.cells(d.row, 7) = worksheets("sheet1").cells(j, 5) - d next d 'turn off filter in column c activesheet.autofiltermode = false 'filter time differences between 0 , 15 minutes activesheet.range("$g:$g").autofilter field:=1, criteria1:=">0:00:00", _ operator:=xland, criteria2:="<0:15:00" 'find minimum value between 0 , 15 minutes minvalue = application.min(searchrange.specialcells(xlcelltypevisible)) 'find row of value of minimum difference minrow = application.match(minvalue, searchrange, 0) 'copy info columns c , d of minimum value's row cells(j, 9).value = cells(minrow, 3) cells(j, 10).value = cells(minrow, 4) activesheet.autofiltermode = false next j stoptime = time elapsedtime = (stoptime - starttime) * 24 * 60 * 60 application.screenupdating = true application.calculation = xlcalculationautomatic msgbox "elapsed time, screen updating on: " & elapsedtime & _ " sec." application.screenupdating = true application.calculation = xlcalculationautomatic end sub

why don't order raw info first (they dates/time right?)? because partition raw info sectors, , given reference value in sector. doesn't take long order set of info column (and secondary sorting on column).

the advantage beingness have 1 time many references you'll utilize on ordered data...

edit create reply more explicit (after comment )

no. don't think need re-organized info much @ all. see excel file:

i'm working on barcodes, order dates or other value. "random" column there can replace info in random orders after i've tested code.

let's i'm sorting barcodes (there's 200 rows in sheet, base of operations principle on many rows have), this. first, need phone call function sorts raw data. can primary sort (i sort column first), in case of equality have 2nd sorting value. can have more that, search sort method:

private sub sorting_all() dim test range set test = range("j" & 200) sheet1.range("a1", sheet1.cells(200, 10)).sort key1:=sheet1.range("a1"), order1:=xlascending, key2:=sheet1.range(columns(8).address()), order2:=xldescending, header:=xlyes, orientation:=xlsortcolumns end sub

then have function finds given value:

function findvalue(myvalue long) range dim numintervales integer, startat long, integer, myintervales variant, cutoff long 'as long not integer don't overflow numintervales = 4 'or whatever, set according data. determine programatically myintervales = getintervales(numintervales) = 1 numintervales - 1 'because if want 4 intervales, means 3 cutoff points cutoff = sheet1.cells(myintervales(i), 1).value if myvalue <= cutoff startat = myintervales(i - 1) 'if myvalue < cutoff #1, want start @ myintervales point between 0-1 exit elseif = numintervales startat = myintervales(numintervales - 1) end if next set findvalue = sheet1.cells.find(what:=myvalue, after:=sheet1.cells((startat + 1), 1), lookin:=xlformulas, lookat:= _ xlpart, searchorder:=xlbyrows, searchdirection:=xlprevious, matchcase:=false) end function

that functions rely on finding range of data, function:

function findrange(mysheet worksheet, byrow_or_bycol string) range 'just find extend of info if byrow_or_bycol = "byrow" set findrange = mysheet.cells.find(what:="*", after:=mysheet.cells(1, 1), lookin:=xlformulas, lookat:= _ xlpart, searchorder:=xlbyrows, searchdirection:=xlprevious, matchcase:=false) else set findrange = mysheet.cells.find(what:="*", after:=mysheet.cells(1, 1), lookin:=xlformulas, lookat:= _ xlpart, searchorder:=xlbycolumns, searchdirection:=xlprevious, matchcase:=false) end if end function

and on dividing raw info appropriate intervales, this:

function getintervales(numintervales) variant dim myintervales() integer, integer, myrange range, mystep long set myrange = findrange(sheet1, "byrow") mystep = round(myrange.row / (numintervales)) 'in case, 200 rows & 4 intervales = 50 rows per intervale = 0 (numintervales - 1) 'because array index start @ 1 redim preserve myintervales(i) ' myintervales(i) = mystep * (i) + 1 'because row(0) doesn't exist next getintervales = myintervales end function

finally need sort of main sort coordinate this:

private sub main() phone call sorting_all msgbox ("this result " & findvalue(sheet2.cells(1, 1).value).row) end sub

that's partly based on random test code project i'm on, it's not finished you'll thought (for illustration range sort method hard-coded, want find programatically).

you fancier this, illustration when import info add together info @ end of (already sorted) data, , phone call sorting function on newly added info (so don't sort 10 000 rows if added 900 unsorted rows it).

you determine programatically intervales set, illustration saying want intervales on 1000 or 2000 rows (just determines works best speed/efficiency).

finally, if you're going adding lots of data, mentionned devise scheme 1 time sheet gets 10 000 rows (for example), starts new sheet , works off one... 1 time again if info sorted, can code know sheet1 has info date xxxx date yyyy, etc.

excel vba excel-vba runtime autofilter