excel - 'ClearContents' and 'PasteSpecial' performance -

in excel vba need clear large named range of content (but not formats) , paste part of formulas taken different named range. issue performance of clear , paste operations. quite slow. same operations performed manually on sheet faster. sheet in manual calculation mode. below code have written this:

sub loadformuals_(nm_rng_formula string, nm_rng_control string, nm_rng_paste string, nm_rng_clear string) dim rabs integer dim rrel integer  dim rng_formula range: set rng_formula = range(nm_rng_formula) dim rng_control range: set rng_control = range(nm_rng_control) dim rng_paste range: set rng_paste = range(nm_rng_paste) dim rng_clear range: set rng_clear = range(nm_rng_clear) application.screenupdating = false application.enableevents = false   dim ws worksheet set ws = worksheets(rng_paste.worksheet.name) ws.enablecalculation = false rng_clear.clearcontents   if not (isempty(rng_control.cells(1, 1)))     application.screenupdating = false     rabs = rng_control.end(xldown).row     rrel = rabs - rng_control.cells(1, 1).row + 1     rng_formula.copy     ws.range(rng_paste.cells(1, 1), rng_paste.cells(rrel, 1)).pastespecial paste:=xlpasteformulas     application.cutcopymode = false end if application.enableevents = true ws.enablecalculation = true end sub   

here nm_rng_clear string name of range needs cleared, nm_rng_formula name of range formulas, , ranges nm_rng_control , nm_rng_pastecontrol formulas pasted.

the slow parts are:

rng_clear.clearcontents  

and:

ws.range(rng_paste.cells(1, 1), rng_paste.cells(rrel, 1)).pastespecial paste:=xlpasteformulas   

is there can make quicker?

i have updated code tiny bit , think perform little bit better it's hard say. slowing down workbook large reference formulas using whole columns: vlookup, match... or in rare cases conditions =if(a:a=2,a:a-b:b,a:a*c:c)

option explicit  sub loadformuals_(byval nm_rng_formula string, _                   byval nm_rng_control string, _                   byval nm_rng_paste string, _                   byval nm_rng_clear string)      dim rabs integer     dim rrel integer     dim strformula string      call turnextrasoff      dim rng_formula range: set rng_formula = range(nm_rng_formula)     dim rng_control range: set rng_control = range(nm_rng_control)     dim rng_paste range: set rng_paste = range(nm_rng_paste)     dim rng_clear range: set rng_clear = range(nm_rng_clear)       dim ws worksheet     set ws = worksheets(rng_paste.worksheet.name)     strformula = rng_formula.resize(1, 1).formula      'clear range     rng_clear.value = vbnullstring      ' paste formulas     if not (isempty(rng_control.cells(1, 1)))         rabs = rng_control.end(xldown).row         rrel = rabs - rng_control.cells(1, 1).row + 1         ws.range(rng_paste.cells(1, 1), rng_paste.cells(rrel, 1)).formula = strformula     end if      call turnextrason  end sub  sub turnextrasoff()     application         .screenupdating = false         .enableevents = false         .calculation = xlcalculationmanual     end end sub  sub turnextrason()     application         .screenupdating = true         .enableevents = true         .calculation = xlcalculationautomatic     end end sub 

hope helps. :)