CODEWITHSUNDEEP
excelvba
lydia
lydia

Reputation: 3

Delete entire row based on row number VBA

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 :)

Upvotes: 0

Views: 6694

Answers (2)

ERT
ERT

Reputation: 729

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.

Upvotes: 1

m.nachury
m.nachury

Reputation: 992

Try this:

Worksheets.Rows(i).Delete

With i as your row number.

Nb: It will cause a shit into your rows number

Upvotes: 0

Related Questions