VBA Code


Clearing Data

There is a way to clear a worksheet model of data and leave the formulas intact.

Range("A1").SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                         Value:=xlSpecialCellsValue.xlNumbers).ClearContents.



xlCVError.xlErrRef 


Removing a Formula

Selection.Formulas = Selection.Value 
Range("D4").Formula = "=SUM(B5:D20)"
Range("F5").Formula = "=5-10*RAND()"
Cells(1,2).Formula = "=G20"


Range("E2").FormulaR1C1 = "=R[-2]C[2]-R[-2]C[3]" 
Range("E2").FormulaR1C1 = "=RC[-1]+RC[-3]-(8/24)"


Entering dates

Range("A1").FormulaR1C1 = "=DATEVALUE(""01/07/77"")+30 
Range("A1").NumberFormat = "mmm-dd-yy"


Converting Formulas between A1 and R1C1

Converting Formulas



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext