VBA for deleting data in row but not formulasSolved/Closed

Question

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


Configuration: Windows 7 / Internet Explorer 9.0

TrowaD · Accepted Answer

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

Debs269 · Answer

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 · Answer

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 · Answer

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

TrowaD · Answer

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 · Answer

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 · Answer

Please ignore my last message

All working fine ..... thank you so much for you help Trowa

VBA for deleting data in row but not formulas

7 responses

Similar discussions

Subscribe To Our Newsletter!