How to delete rows that have just been cut & pasted into another sheet

Question

I've code which cuts and pastes rows into a second sheet where there is a cell containing "Yes".

How do I delete the remaining blank row from the source sheet?

Option Explicit

Sub Copy_n_Paste()
    On Error Resume Next

    Dim srchtrm As String
    Dim rng As Range, destRow As Long
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim c As Range
    Dim i As Integer
    Dim Today As Date

    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    Set shtSrc = Sheets("DOCS")
    Set shtDest = Sheets("ARCHIVE")
    destRow = Worksheets("ARCHIVE").UsedRange.Rows.Count

    Set rng = Application.Intersect(shtSrc.Range("R2:R1000"), shtSrc.UsedRange)

    For Each c In rng.Cells
        If c.Value = "Yes" Then

            c.EntireRow.Cut shtDest.Cells(destRow, 1)

            destRow = destRow + 1

        End If
    Next

    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

    Application.CutCopyMode = False
    Sheets("DOCS").Range("A1").Select

End Sub

Side note - take a look at [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for a better way to find the last row and avoid using `UsedRange`. — BigBen, Jan 14 '20 at 15:06

score 0 · Answer 1 · answered Jan 14 '20 at 15:30

0

This will delete the entire row:

c.EntireRow.Delete

Be careful of it inside of a loop, though. There's a much more thorough discussion here: Delete a row in Excel VBA

answered Jan 14 '20 at 15:30

rmbradburn

308
1
12

How to delete rows that have just been cut & pasted into another sheet

1 Answers1