VBA for deleting data in row but not formulas

Solved/Closed
Debs269 Posts 23 Registration date Monday October 15, 2012 Status Member Last seen August 18, 2016 - Oct 15, 2012 at 06:50 AM
Debs269 Posts 23 Registration date Monday October 15, 2012 Status Member Last seen August 18, 2016 - Oct 31, 2012 at 07:24 AM
Hello,

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,

Any help would be much appreciated

Thx


Related:

7 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
Oct 15, 2012 at 09:20 AM
Hi Debs,

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

Best regards,
Trowa
4