Excel - VBA code for deleting data in row while keeping formulas

Ask a question


Issue


I am working on a spreadsheet where I have to copy data over to a new sheet. Once the data is copied I want to delete the data in the row from original sheet, without losing the formulas. How can this be implemented in VBA?

Solution


Once you have implemented the code below, select a cell from the row you want to keep the formula's from and run the code.

Sub KeepFormulas()
Dim sRow, lCol As Integer
sRow = ActiveCell.Row
lCol = Cells(sRow, Columns.Count).End(xlToLeft).Column
For Each cell In Range(Cells(sRow, 1), Cells(sRow, lCol))
    If cell.HasFormula = False Then cell.ClearContents
Next cell
End Sub


Thanks to MSL for this tip.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team