Makro Performance

embi_

New Member
Joined
Jul 4, 2019
Messages
6
Hey friends,

I have two sheets where I have the same colored cells that each have a number. On the first sheet I only want the numbers below 50 and in the other above 50.
I wrote a code for a command button to do so. The one code that deletes all the numbers above 50 takes about half a second to go through these ~12000 cells.
The second one takes over 2 minutes to delete the values below 50.
Does anybody know what factor there comes into play, that the second one is so much slower?

The codes are:

Delete values above 50:

Code:
Private Sub CommandButton1_Click()
For Each cell In Range("E14:EF108")
  If cell.Value >= 50 Then
    cell.ClearContents
    cell.Interior.Color = RGB(255, 255, 255)
    cell.Interior.Pattern = xlSolid
  End If
Next cell


End Sub

Delete values below 50:

Code:
Private Sub CommandButton1_Click()
For Each cell In Range("E14:EF108")
  If cell.Value < 50 Then
    cell.ClearContents
    cell.Interior.Color = RGB(255, 255, 255)
  End If
Next cell


End Sub
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,037
Office Version
  1. 365
Platform
  1. Windows
Try this

Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim r As Range: Set r = Range("E14:EF108")
Dim AR() As Variant: AR = r.Value

For i = LBound(AR) To UBound(AR)
    For j = LBound(AR) To UBound(AR, 2)
        If AR(i, j) >= 50 Then
            AR(i, j) = vbNullString
            Cells(i + 13, j + 4).Interior.Color = RGB(255, 255, 255)
            Cells(i + 13, j + 4).Interior.Pattern = xlSolid
        End If
    Next j
Next i

r.Value = AR
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Does anybody know what factor there comes into play, that the second one is so much slower?
Are there a lot of blank cells in the range? They would meet the criteria of < 50 so there might be a lot more to process the way your code stands.
 

embi_

New Member
Joined
Jul 4, 2019
Messages
6
Are there a lot of blank cells in the range? They would meet the criteria of < 50 so there might be a lot more to process the way your code stands.


Yes! That was the problem!

Code:
Private Sub CommandButton1_Click()
For Each cell In Range("E14:EF108")
  If cell.Value > 0 And cell.Value < 50 Then
    cell.ClearContents
    cell.Interior.Color = RGB(255, 255, 255)
  End If
Next cell


End Sub

With this code its way faster! Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top