Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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
 -
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 -
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 replies

Answer 1 / 2
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
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
Thank you
Thank you

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

CCM 2942 users have said thank you to us this month

Report
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 2 / 2
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
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
Blocked Profile
Very elegant, once again Sir!
Report
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220 > Blocked Profile
Thanks Mark.
Just following your edict of having fun!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!