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

April 2017



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.

Related


Published by deri58. Latest update on November 7, 2012 at 07:05 AM by deri58.
This document, titled "Excel - VBA code for deleting data in row while keeping formulas," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).