Delete empty ("") rows VBA

Question

What I'm looking for:

I have an Excel workbook, which is build using power query. (works perfectly)
I have a VBA script to refresh the power query every 4 hours (works perfectly)
I have a VBA script to extract all sheets to csv (works perfectly)

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 ""...

If you are really substituting in "", then your problem lies elsewhere. You can try TRIM as well to get rid of leading/lagging spaces — urdearboy
– urdearboy, Commented Oct 20, 2018 at 19:41
Either way, there isn't enough info to provide a solution. Where are you looking for the blank cells? What column? — urdearboy
– urdearboy, Commented Oct 20, 2018 at 19:42

urdearboy · Accepted Answer · 2018-10-20 20:26:55Z

4

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

edited Oct 20, 2018 at 20:26

answered Oct 20, 2018 at 20:03

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shai Rado Over a year ago

one small comment though, if you will add many cells to DeleteMe you better change the order of your If, use If Not DeleteMe Is Nothing then , as all the times after the first time will enter the If and then go to the Else section. SO if you reverse the order you go directly to the If. Hope I made sense

Mitch · Accepted Answer · 2018-10-20 19:48:16Z

0

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!

answered Oct 20, 2018 at 19:48

Mitch

271 silver badge9 bronze badges

1 Comment

shahkalpesh Over a year ago

Set to Application.ScreenUpdating = False before the loop, set it to True after the loop.

Collectives™ on Stack Overflow

Delete empty ("") rows VBA

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related