Previous Topic Next Topic

Script is really slow and sometimes crashes

Solved/Closed
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017 - Updated on Jul 6, 2017 at 06:05 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - 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.
Related:

2 responses

Answer 1 / 2
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Updated on Jul 7, 2017 at 07:26 AM
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.
1
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017
Jul 7, 2017 at 06:57 PM
Thank you very much sensei

all of them works now my problem is what to use lol
0
Answer 2 / 2
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jul 7, 2017 at 07:23 PM
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.
0
Blocked Profile
Jul 7, 2017 at 09:31 PM
Very elegant, once again Sir!
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259 > Blocked Profile
Jul 7, 2017 at 11:42 PM
Thanks Mark.
Just following your edict of having fun!
0