Report

Script is really slow and sometimes crashes [Solved]

Ask a question josh07429 15Posts Friday June 23, 2017Registration date July 7, 2017 Last seen - Last answered on Jul 7, 2017 at 11:42 PM by vcoolio
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.
Helpful
+1
plus moins
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.
Was this answer helpful?  
josh07429 15Posts Friday June 23, 2017Registration date July 7, 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
Reply
Leave a comment
Helpful
+0
plus moins
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 8000Posts Monday June 3, 2013Registration date ModeratorStatus October 19, 2017 Last seen - Jul 7, 2017 at 09:31 PM
Very elegant, once again Sir!
Reply
vcoolio 1070Posts Thursday July 24, 2014Registration date ModeratorStatus October 15, 2017 Last seen - Jul 7, 2017 at 11:42 PM
Thanks Mark.
Just following your edict of having fun!
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!