Delete entire row based on row number VBA

Question

I have a list of 18,806 rows (worksheet named "Reference") that need to be deleted from a 90,000+ row excel sheet (worksheet named "To Delete"). I'm trying to create an array containing the row numbers in "Reference", iterate through the array, and delete each row in the array from "To Delete". So far I have:

    Sub deleteRows()

        Dim rowArray As Variant
        ReDim rowArray(18085) As Integer
        rowArray = Sheets("Reference").Range("A1:A18086").Value

        Dim Arr As Variant
        Dim del As Integer
        Dim i As Integer
        i = 1
        For Each Arr In rowArray
            del = Arr
            Sheets("To Delete").Cells(del, 1).EntireRow.Clear
        Next

    End Sub

Edit: Figured it out! It just clears contents and has some memory overflow errors but I'm working around that. Just wanted to post here for future reference :)

Already answered on [delete a row in excel vba](https://stackoverflow.com/questions/7851859/delete-a-row-in-excel-vba) I think — m.nachury, Jul 11 '17 at 14:44
Wouldn't you run into a slight problem? Let's say I want to delete rows 2 and 4. I run this loop once, which deletes row 2, but now, row 4 gets moved up to row 3. Now I am deleting the original row 5 when I loop again and delete the current 4th row. Did you consider this? — ERT, Jul 11 '17 at 14:47
@m.nachury Tried that--deleted a whole bunch of rows I didn't want deleted, so I'm trying to code my own and getting this error :( — lydia, Jul 11 '17 at 14:47
@E.Trauger yes I realized that as soon as I posted, changed it to clear instead. They're in big chunks so I don't mind going through after to delete the empty spaces, just need to locate where they are without human error — lydia, Jul 11 '17 at 14:48
This will only work if your list of rows are in descending order, highest row number to lowest row number. Otherwise if you have `2,5,7` when row 2 is deleted row 5 just became row 4 and will be skipped and what was row 6 will be deleted in its place. but if you do `7,5,2` the row order will not be changed as rows are deleted. — Scott Craner, Jul 11 '17 at 14:53
@lydia it is good to see that you are doing your own debugging; it shows a lot of effort. Remember to declare all variables (including i and j) before you use them. Also, try changing `For i = LBound(rowArray) To UBound(rowArray)` to `For k= 0 To j-1` *after* you have declared your variables i and j (the same way you declared your array) — ERT, Jul 11 '17 at 16:16

ERT · Accepted Answer · 2017-07-11T15:22:29.353

1

Based on my previous comment, I offer a suggestion to not shift your row numbers:

For Each a In rowArray
    del = rowArray(a)
    Worksheets.Rows(del).ClearContents
Next a

Dim rowNum as Integer
rowNum = Worksheets.Rows.RowCount
While rowNum > 0
    If Worksheets.Cells(rowNum,1).Value = "" Then
        Worksheets.Rows(rowNum).Delete
    End If
    rowNum = rowNum - 1
Loop

Here is the code after the workup. This should be almost to the point of being usable:

Sub deleteRows()
    Dim rowArray(18086) As Integer
    Dim i As Integer, j As Integer, del As Integer, rowNum As Integer
    i = 1
    j = 18086
    While i <= j
        rowArray(i) = Sheets("Reference").Range(i, 1).Value
        i = i + 1
    Loop

    For Each a In rowArray
        del = rowArray(a)
        Sheets("Reference").Rows(del).ClearContents
    Next a

    rowNum = Sheets("Reference").Rows.RowCount
    While rowNum > 0
        If Sheets("Reference").Cells(rowNum, 1).Value = "" Then
              Sheets("Reference").Rows(rowNum).Delete
        End If
        rowNum = rowNum - 1
    Loop
End Sub

Make sure you are defining your variables before you call them, for safety. This is a universal rule in code.

edited Jul 11 '17 at 15:22

answered Jul 11 '17 at 14:53

ERT

719
5
16

1

You will want to loop backwards on the delete, if there are two rows empty back to back it will skip the second. – Scott Craner Jul 11 '17 at 14:55
I'm getting a Compile error: method or data member not found? Not sure what that means, could be because I've changed the module so much.. – lydia Jul 11 '17 at 15:06
This code may not be able to be directly copy-pasted. It all depends on how you defined your worksheets, etc. If you paste your entire code, as is, I can help you. Without that, I can only guide you in the correct direction, which this code does! – ERT Jul 11 '17 at 15:08
Thanks--it's my first time posting on here if you can't tell :) – lydia Jul 11 '17 at 15:10
Try adding an "EDIT" section to your original post so we can see the formatting. – ERT Jul 11 '17 at 15:11
@lydia it's quite alright! Just trying to help out where possible. If you keep coming back, you will want to know the conventions of posting so you don't anger people who are more bothered by these little rules, that's all! – ERT Jul 11 '17 at 15:13
@E.Trauger I think I got it now, what's posted is the most recent version of what I have! – lydia Jul 11 '17 at 15:15
@E.Trauger sorry should have clarified--still getting a method or data member not defined error, but what's posted in my question is the most recent version of my code. Thanks again! – lydia Jul 11 '17 at 15:21
Added more code in the most recent edit. See if that works for you. – ERT Jul 11 '17 at 15:25
@E.Trauger Still getting that pesky Application-defined or Object-defined error, now on the line after that first while loop – lydia Jul 11 '17 at 15:33
@lydia try changing `rowArray(i) = Sheets("Reference").Range(i, 1).Value` to `rowArray(i) = Sheets("Reference").Cells(i, 1).Value` – ERT Jul 11 '17 at 15:35
@E.Trauger I think I found the biggest problem--the values in my original reference column are not copying to the array. When I printed it, they're all 0s. I commented out everything else because this definitely needs to be solved first. I'll update my question again with the most recent version of everything! – lydia Jul 11 '17 at 16:04
@E.Trauger I apparently don't have enough reputation to properly vote and thank you but I finally cracked it and wanted to say thanks again for all the help! :) – lydia Jul 11 '17 at 17:39
@lydia Not a problem! Happy to help, and glad to hear it worked. Pass it on! – ERT Jul 11 '17 at 17:40

m.nachury · Answer 2 · 2017-07-11T14:51:42.967

0

Try this:

Worksheets.Rows(i).Delete

With i as your row number.

Nb: It will cause a shit into your rows number

edited Jul 11 '17 at 14:51

answered Jul 11 '17 at 14:43

m.nachury

972
8
23

That gives me an object required error, sorry I haven't coded in VBA much so the syntax of things is a bit lost on me – lydia Jul 11 '17 at 14:50
to retake your exact code : Worksheets.Rows(i).Delete – m.nachury Jul 11 '17 at 14:51

Delete entire row based on row number VBA

2 Answers2