VBA code running slowly

[Coralie2]

Hello,

I have a problem with my VBA code, it takes a lot of time to execute even if the code seems quite simple...
For the context, I have a very large sheet with a pattern of 3 columns (Month, Year and Value) and I want to delete the three of them if the value cell is empty. It has around 1000 columns and 2000 rows. The code works but takes ages, and after a while Excel freezes and stops working

Here is my current code :

Sub del_empty()


Dim NbCol, i As Long
Dim NbRow, j As Long


NbCol = Cells(1, Rows(1).Cells.Count).End(xlToLeft).Column


For i = NbCol To 1 Step -3


NbRow = Cells(Rows.Count, i).End(xlUp).Row
    
    For j = NbRow - 1 To 2 Step -1
        
        If Cells(j + 1, i) = "" Then
        Cells(j + 1, i - 2).Delete
        Cells(j + 1, i - 1).Delete
        Cells(j + 1, i).Delete
        End If
    Next
Next


End Sub

Thank you for reading me !

Coralie (I'm french, sorry for my English )

 

[Joe4]

Re: VBA code running slowly - please help me !

Welcome to the Board!

Try these minor changes to see if they help:

Sub del_empty()

Dim NbCol [COLOR=#ff0000]As Long[/COLOR], i As Long
Dim NbRow [COLOR=#ff0000]As Long[/COLOR], j As Long

[COLOR=#ff0000]Application.ScreenUpdating = False[/COLOR]

NbCol = Cells(1, Rows(1).Cells.Count).End(xlToLeft).Column

For i = NbCol To 1 Step -3

NbRow = Cells(Rows.Count, i).End(xlUp).Row
    
    For j = NbRow - 1 To 2 Step -1
        
        If Cells(j + 1, i) = "" Then
        Cells(j + 1, i - 2).Delete
        Cells(j + 1, i - 1).Delete
        Cells(j + 1, i).Delete
        End If
    Next
Next

[COLOR=#ff0000]Application.ScreenUpdating = True[/COLOR]

End Sub

Suppressing the screen updating until the end usually helps speed things up when you are making updates (especially via loops).

Also, it shouldn't really affect the performance, but in declaring your variables, each one need to be explicitly declared, like I show above.
The way you had written it, NbCol and NbRow would have been declared as Variant.

 

[Fennek]

Re: VBA code running slowly - please help me !

Hi,

you find the slowest process in xl!

As your starting point are empty cells, try something like this:

with activesheet.usedrange.specialcells(4)
   .offset(,-2).clear
   .offset(,-1).clear
   .clear
end with

regards