CODEWITHSUNDEEP
excelvba
Mitch
Mitch

Reputation: 27

Delete empty ("") rows VBA

What I'm looking for:

But there is a catch, the sheets are build with special characters, so I did create a extra sheet, with all substitutions you could think... (also works perfectly)

but this extra sheet is using information of 3000 rows. (based on the table extracted from power query). The power query is living, sometimes I need 2000 rows, sometimes 2800 +, this is why I did make the extra sheet based on 3000 rows...

With the formula:

=IFERROR(substitutions.....;"")

The cells on the bottom of the file looks empty, because I did use the "" in the formula, but actually the cells are not empty... So I need a VBA script which will look to the blank cells which are created with ""...

Upvotes: 0

Views: 5971

Answers (2)

urdearboy
urdearboy

Reputation: 14580

This will be quicker. Instead of deleting cells in your loop, do so outside of the loop. This will result in only one instance of rows being deleted. 

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Dim rng As Range, myCell As Range, DeleteMe As Range

'Loop to find rows to delete
For Each myCell In ws.Range("A1:A3000")
    If myCell = "" Then
        If DeleteMe Is Nothing Then
            Set DeleteMe = myCell
        Else
            Set DeleteMe = Union(DeleteMe, myCell)
        End If
    End If
Next myCell

'Delete rows here
If Not DeleteMe Is Nothing Then DeleteMe.EntireRow.Delete

End Sub

Upvotes: 4

Mitch
Mitch

Reputation: 27

I did found a solution: 

Sub DeleteRowsWithEmptyColumnDCell()
Dim rng As Range
Dim i As Long
Set rng = ThisWorkbook.ActiveSheet.Range("A1:A3000")
With rng
    ' Loop through all cells of the range
    ' Loop backwards, hence the "Step -1"
    For i = .Rows.Count To 1 Step -1
        If .Item(i) = "" Then
            ' Since cell is empty, delete the whole row
            .Item(i).EntireRow.Delete
        End If
    Next i
End With
End Sub

But it is slow & takes high memory usage... I hope some one has a better solution!

Upvotes: 0

Related Questions