Hi,
I have this code:
The first part is going through certain cells and scrubbing data if a certain condition is met. The second part is clearing the contents of blank cells so that the COUNT function works properly with those cells. However, this code takes really long to loop through the 300+rows (about 40 mins). Is there a way that I can speed this up
Thanks
I have this code:
Code:
For rw = 2 To num
Application.StatusBar = String(3, ChrW(9609)) & "Scrubbing at row " & rw & "/" & num & "."
If InStr(Range("L" & rw).Value, "1") > 0 Then
Range("A" & rw).Value = "#"
Range("K" & rw).Value = "#"
Range("M" & rw).Value = "#"
End If
If InStr(Range("L" & rw).Value, "2") > 0 Then
Range("A" & rw).Value = "#"
Range("K" & rw).Value = "#"
Range("M" & rw).Value = "#"
End If
If InStr(Range("L" & rw).Value, "3") > 0 Then
Range("A" & rw).Value = "#"
Range("K" & rw).Value = "#"
Range("M" & rw).Value = "#"
End If
If InStr(Range("L" & rw).Value, "11") > 0 Then
Range("A" & rw).Value = "#"
Range("K" & rw).Value = "#"
Range("M" & rw).Value = "#"
End If
If InStr(Range("L" & rw).Value, "23") > 0 Then
Range("A" & rw).Value = "#"
Range("K" & rw).Value = "#"
Range("M" & rw).Value = "#"
End If
If InStr(Range("L" & rw).Value, "39") > 0 Then
Range("A" & rw).Value = "#"
Range("K" & rw).Value = "#"
Range("M" & rw).Value = "#"
End If
If Range("E" & rw).Value = "" Then
Range("E" & rw).ClearContents
End If
If Range("F" & rw).Value = "" Then
Range("F" & rw).ClearContents
End If
If Range("G" & rw).Value = "" Then
Range("G" & rw).ClearContents
End If
If Range("H" & rw).Value = "" Then
Range("H" & rw).ClearContents
End If
If Range("I" & rw).Value = "" Then
Range("I" & rw).ClearContents
End If
If Range("J" & rw).Value = "" Then
Range("J" & rw).ClearContents
End If
Next rw
The first part is going through certain cells and scrubbing data if a certain condition is met. The second part is clearing the contents of blank cells so that the COUNT function works properly with those cells. However, this code takes really long to loop through the 300+rows (about 40 mins). Is there a way that I can speed this up
Thanks