VBA for deleting data in row but not formulas [Solved/Closed]

Debs269 23 Posts Monday October 15, 2012Registration date August 18, 2016 Last seen - Oct 15, 2012 at 06:50 AM - Latest reply: Debs269 23 Posts Monday October 15, 2012Registration date August 18, 2016 Last seen
- 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


See more 

7 replies

Best answer
TrowaD 2391 Posts Sunday September 12, 2010Registration dateContributorStatus July 12, 2018 Last seen - Oct 15, 2012 at 09:20 AM
4
Thank you
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

Thank you, TrowaD 4

Something to say? Add comment

CCM has helped 1739 users this month

TrowaD 2391 Posts Sunday September 12, 2010Registration dateContributorStatus July 12, 2018 Last seen - Oct 16, 2012 at 09:50 AM
3
Thank you
Hi Debs,

1. "Can I ask how do I link that into my current code.. "

In your current code you can "call" the KeepFormulas macro:
Call KeepFormulas

2. "the code currently looks for the criteria of closed.. and deletes..."

What do you mean by closed and deletes? Are those values?

3. "like I have said dont want to delete the row, and want to keep the formulas.."

The provided code should do that.

4. "Header row 7, data from row 8,"

If it is always row 8 which needs to be checked then change this line:
sRow = ActiveCell.Row
into:
sRow = 8

Or to clean up the code:
Sub KeepFormulas()
Dim lCol As Integer
lCol = Cells(8, Columns.Count).End(xlToLeft).Column
For Each cell In Range(Cells(8, 1), Cells(8, lCol))
    If cell.HasFormula = False Then cell.ClearContents
Next cell
End Sub

Ask away if something is unclear; I'm here to help.

Best regards,
Trowa
Debs269 23 Posts Monday October 15, 2012Registration date August 18, 2016 Last seen - Oct 15, 2012 at 09:59 AM
2
Thank you
Thanks Trowa

Can I ask how do I link that into my current code..
the code currently looks for the criteria of closed.. and deletes...
like I have said dont want to delete the row, and want to keep the formulas..

sorry should have stated that...

Header row 7, data from row 8,

Can you assist...
Many Thanks
TrowaD 2391 Posts Sunday September 12, 2010Registration dateContributorStatus July 12, 2018 Last seen - Oct 18, 2012 at 09:56 AM
1
Thank you
Hi Debs,

The folowing code needs to be implemented into the "Open Issues" sheet.
Right click on the sheets name and select "view code", paste the code in the big white field.

The code looks at column D. Whenever a value in this column is changed to "Closed", then the entire row is copied to the first available row on the "Closed Issues" sheet as values. On the "Open Issues" sheet the copied row will be cleared except for the formula's.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lCol As Integer
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub
If Target.Value = "Closed" Then
    lCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    Range(Cells(Target.Row, 1), Cells(Target.Row, lCol)).Copy
    Sheets("Closed Issues").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    For Each cell In Range(Cells(Target.Row, 1), Cells(Target.Row, lCol))
        If cell.HasFormula = False Then cell.ClearContents
    Next cell
End If
Application.CutCopyMode = False
End Sub


Hope you like.

Best regards,
Trowa
Debs269 23 Posts Monday October 15, 2012Registration date August 18, 2016 Last seen - Oct 17, 2012 at 03:50 AM
0
Thank you
Thanks Trowa

Again I have not used this type of forum before so think I need to be clearer on what I am asking..

What I am looking to do is..

Sheet "Open Issues" has rows of data, when the issue is change from open to closed (column D) I want to copy all closed rows to sheet "Closed Issues" and then clear contents (leaving Formulas)of all "closed" on the "open issues" sheet...

This will be updated regularly...and need to pick up next avaiable line on sheet "Closed issues" to copy into...

Really appreciate your help on this


Thanks
Debs269 23 Posts Monday October 15, 2012Registration date August 18, 2016 Last seen - Oct 31, 2012 at 03:29 AM
0
Thank you
Thank you works great, only one issue...when copies closed into the "Closed Issues" sheet it is not finding the next available row..so copying over the data that was on there...

Thanks again for your help
Debs269 23 Posts Monday October 15, 2012Registration date August 18, 2016 Last seen - Oct 31, 2012 at 07:24 AM
0
Thank you
Please ignore my last message

All working fine ..... thank you so much for you help Trowa