Script is really slow and sometimes crashes [Solved]

josh07429 16 Posts Friday June 23, 2017Registration date November 20, 2017 Last seen - Jul 6, 2017 at 05:25 PM - Latest reply: vcoolio 1171 Posts Thursday July 24, 2014Registration dateModeratorStatus June 21, 2018 Last seen
- Jul 7, 2017 at 11:42 PM
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.
See more 

Your reply

5 replies

Best answer
vcoolio 1171 Posts Thursday July 24, 2014Registration dateModeratorStatus June 21, 2018 Last seen - Updated by vcoolio on 7/07/17 at 07:26 AM
1
Thank you
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.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1830 users this month

josh07429 16 Posts Friday June 23, 2017Registration date November 20, 2017 Last seen - Jul 7, 2017 at 06:57 PM
Thank you very much sensei

all of them works now my problem is what to use lol
Respond to vcoolio
vcoolio 1171 Posts Thursday July 24, 2014Registration dateModeratorStatus June 21, 2018 Last seen - Jul 7, 2017 at 07:23 PM
0
Thank you
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.
ac3mark 9588 Posts Monday June 3, 2013Registration dateModeratorStatus June 21, 2018 Last seen - Jul 7, 2017 at 09:31 PM
Very elegant, once again Sir!
vcoolio 1171 Posts Thursday July 24, 2014Registration dateModeratorStatus June 21, 2018 Last seen > ac3mark 9588 Posts Monday June 3, 2013Registration dateModeratorStatus June 21, 2018 Last seen - Jul 7, 2017 at 11:42 PM
Thanks Mark.
Just following your edict of having fun!
Respond to vcoolio