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

December 2016


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?


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.

Related :

This document entitled « Excel - VBA code for deleting data in row while keeping formulas » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.