Menu

Script is really slow and sometimes crashes [Solved/Closed]

Report
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
 -
vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
 -
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
Related:
Answer 1
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
 207
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5778 users have said thank you to us this month

Report
josh07429
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
Thank you very much sensei

all of them works now my problem is what to use lol
Answer 3
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
 207
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.
Report
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
 1573
Very elegant, once again Sir!
Report
vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
 207 > ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
Thanks Mark.
Just following your edict of having fun!
Ask a question