CODEWITHSUNDEEP
excelvba
ruedi
ruedi

Reputation: 5545

Delete empty cells (very slow)

I have a Range where I want to delete all empty cells. I developed the following solution but it is very very slowly. Could anyone help me to understand why this is so solw and what approach would lead to a faster solution.

Application.ScreenUpdating = False
    For i = letzte2 To 4 Step -1
        For j = 2 To 17
            If tab6.Cells(i, j) = "" Then
            tab6.Cells(i, j).Delete shift:=xlUp
            End If
        Next j
    Next i
Application.ScreenUpdating = True

Upvotes: 0

Views: 618

Answers (2)

Netloh
Netloh

Reputation: 4378

This can be done fairly easy (and effective) by using the SpecialCells() method. The following deletes all empty cells in a selection.

Sub DeleteEmptyCells()
    Dim Rng As Range

    Set Rng = Selection.SpecialCells(xlCellTypeBlanks)

    If Not Rng Is Nothing Then
        Rng.Delete shift:=xlUp
    End If
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Perform only one Delete :

Dim rDel As Range
Set rDel = Nothing
Application.ScreenUpdating = False
    For i = Letzte2 To 4 Step -1
        For j = 2 To 17
            If tab6.Cells(i, j) = "" Then
                If rDel Is Nothing Then
                    Set rDel = tab6.Cells(i, j)
                Else
                    Set rDel = Union(rDel, tab6.Cells(i, j))
                End If
            End If
        Next j
    Next i
If Not rDel Is Nothing Then
    rDel.Delete shift:=xlUp
End If
Application.ScreenUpdating = True

Upvotes: 1

Related Questions