CODEWITHSUNDEEP
vbaexcel
user2693583
user2693583

Reputation: 11

Excel VBA - Search certain column for a #N/A, copy entire row to new sheet and delete row

This is what I have already. It searches column B in sheet 1 for #N/A and deletes the row. 

Sub DeleteErrorRows()
    On Error Resume Next    
    Range("B:B").SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
    On Error GoTo 0     
End Sub

What I would like is for it to copy that row onto sheet 2 and then delete? So I can have a record of what was deleted.

Any help would be much appreciated. Thanks!

Upvotes: 1

Views: 715

Answers (1)

Gary's Student
Gary's Student

Reputation: 96763

Consider:

Sub DeleteErrorRows()
    Dim r As Range
    Set r = Range("B:B").SpecialCells(xlCellTypeConstants, 16).EntireRow
    r.Copy Sheets("Sheet2").Range("A1")
    r.Delete
End Sub

Upvotes: 1

Related Questions