excel - macro taking long time to run -

i have macro moving data 1 column another, macro takes long time run(approx. 25-30 mins). data in excel sheets 200,000 rows. since have around 500 excel sheets , if run slow can takes me weeks clean files, there better way similar thing can takes less time.

sub j_priceadjust()   dim j range  dim r range   set j = intersect(activesheet.usedrange, range("j:j"))    ' working on column j   each r in j     if left(r.text, 4) = "page"         r.copy r.offset(0, 2)         r.clear     end if next r  each r in j     if left(r.text, 6) = "amount" or left(r.text, 1) = "$" or left(r.text, 1) = "("         r.copy r.offset(0, 1)         r.clear     end if next r  activeworkbook.save  end sub 

as alternative looping through data per current code, consider using autofilter filter rows contain data want, copying data desired column. i'm not sure if it's still faster once spreadsheets on 200k rows, have seen performance improvements on smaller (but still large) spreadsheets in past.

see code below. first, filters data begins 'page', 2 columns over, places formula copy data (i'm not sure if there's mechanism directly assign value, formula seems work). next, cleared filter, issued new filter amount, placed formula 1 column on data.

after said , done, can write additional line copy pastespecial values formulas added. give try , let know if it's more efficient.

sub makesomechanges()     dim rng range      set rng = activesheet.usedrange.columns(10)      rng.autofilter field:=1, criteria1:="page*"      rng.offset(, 2).formular1c1 = "=rc[-2]"     sheet1.autofiltermode = false      rng.autofilter field:=1, criteria1:="amount*"      rng.offset(, 1).formular1c1 = "=rc[-1]"     sheet1.autofiltermode = false end sub