Script is really slow and sometimes crashesSolved/Closed

Question

Hi,

My script is really slow and sometimes crashes when there's too many rows.

Can someone help me figure out how to make it faster?

 Dim b As Long
    For b = 1 To 1500
    If (ActiveSheet.Range("$J$" & b) = 0) Then
        ActiveSheet.Range("$H$" & b) = ""
  End If
  Next
End Sub

The script deletes anything from Column H cell if cell in Column J is 0

Thanks in advance.

vcoolio · Answer

Hello Josh,

Here is another loop type construct that may be a little quicker:-


Sub DeleteThings()

Dim lr As Long
    Dim c As Range
lr = Range("H" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

For Each c In Range("J2:J" & lr)
    If c.Value = "0" Then
       c.Offset(, -2).ClearContents
       End If
Next c

Application.ScreenUpdating = True

End Sub

or you can use Autofilter which is quicker again on a large data set:-

Sub DeleteThings2()

Dim lr As Long
          
Application.ScreenUpdating = False

Range("J1", Range("J" & Rows.Count).End(xlUp)).AutoFilter 1, 0, , , 7
         lr = Range("H" & Rows.Count).End(xlUp).Row
              If lr > 1 Then
                  Range("H2", Range("H" & Rows.Count).End(xlUp)).ClearContents
                  End If
[J1].AutoFilter

Application.ScreenUpdating = True

End Sub

.......and following is a "sleeker" version of using the Autofilter:-

Sub DeleteThings3()

Application.ScreenUpdating = False

With Sheet1.Range("J1", Range("J" & Rows.Count).End(xlUp))
        .AutoFilter 1, 0
        .Offset(1, -2).ClearContents
        .AutoFilter
End With
   
Application.ScreenUpdating = True

End Sub

Of course you can change Sheet 1 to ActiveSheet if you prefer.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hi Josh,

You're welcome. Glad that I was able to help again.

I'd probably go with the third option especially if your data set is expected to grow larger.

Keep the other two up your sleeve as they can be adapted for other scenarios.

Cheerio,
vcoolio.

Script is really slow and sometimes crashes

2 replies

Similar discussions

Subscribe To Our Newsletter!