Delete Row if criteria is met

[sharky12345]

Guys - I'm using this to copy values to another workbook only if the cell in column A and D are not blank;

For Each Cell In Sheet1.Range("A2:A95")If Cell.Value <> "" Then
If Cell.Offset(0, 3).Value <> "" Then[INDENT]Set ws = DestFile.Sheets("Data")[/INDENT]
[INDENT]iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/INDENT]
[INDENT=2]ws.Cells(iRow, 3).Value = Cell.Value
ws.Cells(iRow, 1).Value = Cell.Offset(0, 2).Value
ws.Cells(iRow, 2).Value = Cell.Offset(0, 3).Value[/INDENT]
[INDENT]End If[/INDENT]
End If
Next

Once that data has been copied, I would like the row containing that data to be deleted, (shift up). Can someone show me how please?

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

    Dim DeleteRow As Range
    Set ws = DestFile.Sheets("Data")
    For Each cell In Sheet1.Range("A2:A95")
        iRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With cell
            If Len(.Value) > 0 And Len(.Offset(0, 3).Value) > 0 Then
                ws.Cells(iRow, 1).Resize(, 3).Value = Array(.Offset(0, 2).Value, .Offset(0, 3).Value, .Value)
                If DeleteRow Is Nothing Then Set DeleteRow = cell Else Set DeleteRow = Union(DeleteRow, cell)
            End If
        End With
    Next cell
    If Not DeleteRow Is Nothing Then DeleteRow.EntireRow.Delete shift:=xlShiftUp

Dave

 

[sharky12345]

That works just fine Dave - thanks so much!

 

[dmt32]

That works just fine Dave - thanks so much!

welcome

Dave