PDA

View Full Version : [SOLVED] working code inside - need to add a delete the row after the data has been copied



markpem
03-30-2015, 06:27 AM
Hello

I currently have code below which works very well.

There are two sheets

"PageToSyncFrom" - This has the data that may contain updated cells to copy over to "PageToSyncTo" - This all works perfectly.

The spreadsheet works by matching reference numbers in Column A. What I need to happen is when a match has been done and the data is synced over to delete (or clear) the row in PageToSyncTo that it has just copied from.

So (the theory) is that the only thing that would be left on PageToSyncTo is data which does not have a reference match in ColumnA on PageToSyncFrom.

I did ask this question a while ago on another forum and I can't remember it and the person advised I should put (but it doesn't seem to work):-



Add this in place of your End If: End If


Code:
End If

Cells(s1rw, col).EntireRow.Delete Shift:=xlUp

End If


So this is my current code:-




' Sync Updates with Converted Data
Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long
Cancel = True ' Ignore error messages as first column is LOCKED
Sheets("PageToSyncFrom").Select
With Sheets("PageToSyncTo")
s2rw = 2 ' Adjust to first data row #
endcol = .Cells(s2rw - 1, 1).End(xlToRight).Column
Do Until .Cells(s2rw, 1).Value = "" ' Loop through case #s
s1rw = 0
On Error Resume Next
s1rw = Cells.Find(What:=.Cells(s2rw, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole).Row
On Error GoTo 0
If s1rw > 0 Then ' Found case #
Application.StatusBar = "Updating cases.. Found: " + Str$(s1rw)
For col = 2 To endcol ' Loop through columns
If Cells(s1rw, col).Value <> "" Then
If IsDate(Cells(s1rw, col).Value) Then
.Cells(s2rw, col).Value = Format(Cells(s1rw, col).Value, "mm/dd/yyyy")
Else
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
End If
End If
Next
End If
s2rw = s2rw + 1
Loop
.Select
End With

p45cal
03-30-2015, 02:12 PM
try the same line:
Cells(s1rw, col).EntireRow.Delete Shift:=xlUp
or the shorter (untested):
Rows(s1rw).delete
between
Next
and
End If

The original solution would delete too many rows as it's deleting a row for each column of data copied over.

markpem
03-31-2015, 02:09 AM
Hello P45cal

Thanks for taking the time to help me out.

This doesn't work, what happens is that it deletes the wrong sheet (and its not a case of changing it to s2rw either)

However in the meantime I seem to have figured it out:-

At the top I

Dim tester as long

then at the bottom I change



If Cells(s1rw, col).Value <> "" Then
If IsDate(Cells(s1rw, col).Value) Then
.Cells(s2rw, col).Value = Format(Cells(s1rw, col).Value, "mm/dd/yyyy")
Rows(tester).Clear
Else
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
Rows(tester).Clear
End If
End If
Next
End If
s2rw = s2rw + 1
Loop
.Select
End With