VBA to delete specific rows by row number

Question

If anyone is willing to help me, I'd be most grateful.

Basically I would like to perform the same task as what was featured in this thread...

Excel Marcro - delete a row based on row number

I have a list of 3500+ lines and need to occasionally delete about 25-30 (non-consecutive) rows based on row number (the row #s will vary every time). I'd like to list the row numbers (to be deleted) on Sheet2 (in Column A) and have the code automatically read the row numbers on Sheet2 and delete those rows from Sheet1. I tried the code provided in the thread and I get a "run-time error 424" Object required. If I mouse over the error it tells me "SourceWks = Nothing" and "Sheet2 = Empty". I do have data contained in the sheet(s). I'm sure I'm just missing something simple.

I'll paste the code I'm using below (again it is from the original thread which was reported by the user to work just fine)...

Dim deleteRows As Range
Dim data() As Variant
Dim i As Double

Dim SourceWks As Worksheet, deleteWks As Worksheet

Set SourceWks = Sheet2
Set deleteWks = Sheet1

    With SourceWks
        data = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    End With

    Set deleteRows = deleteWks.Rows(data(1, 1))

    For i = 2 To UBound(data, 1)

        Set deleteRows = Union(deleteRows, deleteWks.Rows(data(i, 1)))

    Next i

    deleteRows.Delete Shift:=xlUp

I've tried it both ways...keeping Sheet1 named "Sheet1" and Sheet2 named "Sheet2" and I've also tried changing the sheets to be named to: "deleteWKS" and "SourceWks" all with the same results.

If anyone can please let me know what I'm doing incorrectly, I'd be most appreciative.

Do you only have one Excel file open when you run the macro? — tigeravatar, Jan 16 '18 at 21:46
Hi tigeravatar and thanks for your reply. Yes I do (I just re-confirmed I had everything closed out expect the one workbook in question). — NatalieF, Jan 16 '18 at 22:02
Try `Set SourceWks = ActiveWorkbook.Sheets("Sheet2")` where Sheet2 is the actual name of your Source worksheet on the sheet tab. — tigeravatar, Jan 16 '18 at 22:06
Excellent tigeravatar...that did it. Works great now. I very much appreciate your assistance. — NatalieF, Jan 16 '18 at 22:23

score 0 · Accepted Answer · answered Jan 16 '18 at 22:18

Consider:

Sub rowKiller()

    Dim SourceWks As Worksheet, deleteWks As Worksheet
    Dim rng As Range, i As Long

    Set SourceWks = Sheet2
    Set deleteWks = Sheet1

    Set rng = SourceWks.Columns(1).SpecialCells(2)
    rng.Sort Key1:=rng(1), Order1:=xlDescending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal

    For Each r In rng
        i = r.Value
        deleteWks.Rows(i).EntireRow.Delete
    Next r

End Sub

NOTES:

The reason we sort the row list descending because we want to delete the rows in Sheet1 from the bottom upwards.

Thank you as well, Gary's Student. I did have to add tigeravatar's suggestion of "= ActiveWorkbook.Sheets("Sheet2")" but your code works great as well. Thanks much. — NatalieF, Jan 16 '18 at 22:30

VBA to delete specific rows by row number

1 Answers1