Script is really slow and sometimes crashes [Solved]
josh0742916
Posts Friday June 23, 2017Registration date
November 20, 2017
Last seen
-
Jul 6, 2017 at 05:25 PM - Latest reply: vcoolio1171
Posts Thursday July 24, 2014Registration dateModeratorStatus
June 21, 2018
Last seen
- Jul 7, 2017 at 11:42 PM
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.
vcoolio1171
Posts Thursday July 24, 2014Registration dateModeratorStatus
June 21, 2018
Last seen
>
ac3mark9588
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!
has helped 1830 users this month
all of them works now my problem is what to use lol