0

In this sheet, I am trying to search in a range for empty cells, and deleting their respective rows.

Sub Delete()

'Amass row numbers
Dim B, Blank As Range
Dim Deletion() As Long
Dim D As Long

Set Blank = Sheets("Quotation").Range("I17:I3816")

D = 0

For Each B In Blank

    If IsEmpty(B) Then

        D = D + 1

        ReDim Preserve Deletion(D)

        Deletion(D) = B.Row

    End If

Next B

Dim Amass As Range

'A starting point for the Amass range - should it need one pre-Union?
Set Amass = Sheets("Quotation").Range("10000:10000")

'Amass rows
For i = 1 To D

    Set Amass = Union(Amass, Sheets("Quotation").Range(Deletion(i) & ":" & Deletion(i)))

Next i

'Delete rows
Amass.EntireRow.Delete

End Sub

It fails on the last action, with the error:

"Delete method of the range class failed"

Am I using the array and "ReDim Preserve" correctly?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Ben Gerry
  • 15
  • 6
  • Have a look at the methods here --> https://stackoverflow.com/questions/30959315/excel-vba-performance-1-million-rows-delete-rows-containing-a-value-in-less – Ryan Wildry Jan 09 '19 at 13:56
  • 1
    Did you try deleting the rows when a blank cell is found in the range in the loop instead of creating an array? – Pablo G Jan 09 '19 at 14:13
  • Your code works correctly on my PC. Are u sure, that u have list "Quotation" in your workbook? – Alexey C Jan 09 '19 at 14:13

1 Answers1

1

I think something like this is what you're looking for:

Sub tgr()

    Dim ws As Worksheet
    Dim rCheck As Range
    Dim rDel As Range

    Set ws = ActiveWorkbook.Sheets("Quotation")

    For Each rCheck In ws.Range("I17", ws.Cells(ws.Rows.Count, "I").End(xlUp)).Cells
        If IsEmpty(rCheck) Then
            If Not rDel Is Nothing Then Set rDel = Union(rDel, rCheck) Else Set rDel = rCheck
        End If
    Next rCheck

    If Not rDel Is Nothing Then rDel.EntireRow.Delete

End Sub
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • Thank you, Tiger. I will certainly review other options and just choose something a little quicker and easier. I like the way you have whittled down my code into something more compact. One question: your final row reads "... rDel.EntireRow.Delete" with rDel being your collection of rows to be deleted, right? So, in my original code, why does "Amass.EntireRow.Delete" not work? Is there something wrong with my range? – Ben Gerry Jan 09 '19 at 14:33
  • @BenGerry I am unable to reproduce the error you're getting. Your code runs fine in my test workbook. – tigeravatar Jan 09 '19 at 14:36