excel - Delete data in range of cells but retain the formula -
is there way delete data in range of cells retain formula. example file simple calculation sum numbers c2:c5 , put sum in sheets("summary").range("b2"). after clicking button, delete data in range a2:c5, formulas in cells a2:c5 deleted.
my ideal result formula in range(a2:c5) retain.
mycode:
private sub commandbutton1_click() dim sum worksheet, ws worksheet dim c range set sum = sheets("summary"): set ws = sheets("sheet1") sum.range("b" & sum.range("b1000000").end(xlup).offset(1, 0).row) = worksheetfunction.sum(ws.range("c2:c" & ws.range("c100000").end(xlup).row)) 'ws.range("a2:c" & ws.range("a1").specialcells(xlcelltypelastcell).row).clearcontents each c in sheet1.range("a2:c9") if not c.hasformula c.clearcontents next c
end sub
you try code below:
dim c range each c in sheet1.range("a2:c5") if not c.hasformula c.clearcontents next c
Comments
Post a Comment