Screen goes white while sorting

gemini528

Board Regular
Joined
Jun 13, 2013
Messages
53
Hello Excel Gurus,

Whenever I the run code below, the screen goes white for 3 seconds (or until the sorting is done) then the normal black background. What I want is to eliminate the white background while sorting the data. Any suggestion or revision or addition on the code will be appreciated

Thank you very much.

Here is the code:
VBA Code:
Sub Sort()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
  With Sheets("INVTRY")
   .Unprotect Password:="password"
    With .Range("B4:F5000")
      .Cells.Sort Key1:=.Columns(Application.Match("TR DATE", .Rows(1), 0)), Order1:=xlAscending, _
                  Key2:=.Columns(Application.Match("ITEM NO", .Rows(1), 0)), Order2:=xlAscending, _
                  key3:=.Columns(Application.Match("DESC", .Rows(1), 0)), Order3:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlYes 'comma then space then underscore then ENTER key
    End With
   .Protect Password:="password", DrawingObjects:=False, UserInterfaceOnly:=True, Contents:=True, _
                           Scenarios:=True, AllowFiltering:=True, _
                           AllowFormattingCells:=True, AllowSorting:=True
  End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows
That is normal when your cpu is under load. You could try setting calculation to manual while it runs to see if it reduces the load and speeds it up at all (code edited to do this below) beyond that or a more powerful PC you're going to be stuck with what you have.
VBA Code:
Sub Sort()
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
  With Sheets("INVTRY")
   .Unprotect Password:="password"
    With .Range("B4:F5000")
      .Cells.Sort Key1:=.Columns(Application.Match("TR DATE", .Rows(1), 0)), Order1:=xlAscending, _
                  Key2:=.Columns(Application.Match("ITEM NO", .Rows(1), 0)), Order2:=xlAscending, _
                  key3:=.Columns(Application.Match("DESC", .Rows(1), 0)), Order3:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlYes 'comma then space then underscore then ENTER key
    End With
   .Protect Password:="password", DrawingObjects:=False, UserInterfaceOnly:=True, Contents:=True, _
                           Scenarios:=True, AllowFiltering:=True, _
                           AllowFormattingCells:=True, AllowSorting:=True
  End With
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
 

gemini528

Board Regular
Joined
Jun 13, 2013
Messages
53
For the meantime, is there a way to add another sheet with dark background with the message "PROCESSING, PLEASE WAIT..." to be executed? I would appreciate it if you can do an additional or new code. I don't know how to do it, please help.

Thank you so much for your help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows
That would still turn white at the same point, will take longer to run and you would also have an amount of flickering at the beginning and end while the message screen is shown and hidden.

Why are you using application.match in the sort keys, Does the order of the columns change?
Does your data extend down to row 5000, or have you set a bigger range?
 

gemini528

Board Regular
Joined
Jun 13, 2013
Messages
53

ADVERTISEMENT

Yes, the item number is being added once in a while, so it needs to be sorted out automatically. On your second question, right now it is around 2000 rows. I just make it up to 5000 for some rooms in the future. If you can help me improve it, I would appreciate it. Any suggestions are welcome.

Thank you so much...
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows
I think that you misunderstood this bit
Why are you using application.match in the sort keys, Does the order of the columns change?
Your answer here is referring to sorting rows, not columns.
Yes, the item number is being added once in a while, so it needs to be sorted out automatically.
To make it clearer what I was asking:-

Which column is "TR DATE" in? Does that ever move to a different column?
Which column is "ITEM NO" in? Does that ever move to a different column?
Which column is "DESC" in? Does that ever move to a different column?
 

gemini528

Board Regular
Joined
Jun 13, 2013
Messages
53

ADVERTISEMENT

Sorry for the confusion...
TR DATE is column "D", it stay on the same column
ITEM NO is column "B", it stay on the same column
DESC is column "C", and stay on the same column

It is just a regular sorting of Excel.

Thanks for your big help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows
I've made a couple of changes to your code but I can't see that it is going to make much difference, the volume of data that you have should sort almost instantly. This implies that the problem lies elsewhere, perhaps with inefficient formulas causing lag at the point of calculation.
VBA Code:
Sub Sort()
    Dim rw As Long
        Application.Calculation = xlManual
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
    With Sheets("INVTRY")
        .Unprotect Password:="password"
        rw = .Cells(Rows.Count, 2).End(xlUp).Row
        With .Range("B4:F" & rw)
            .Cells.Sort Key1:=.Range("D4:D" & rw), Order1:=xlAscending, _
                        Key2:=.Range("B4:B" & rw), Order2:=xlAscending, _
                        Key3:=.Range("C4:C" & rw), Order3:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlYes 'comma then space then underscore then ENTER key
        End With
        .Protect Password:="password", DrawingObjects:=False, UserInterfaceOnly:=True, Contents:=True, _
                           Scenarios:=True, AllowFiltering:=True, _
                           AllowFormattingCells:=True, AllowSorting:=True
    End With
        Application.Calculation = xlAutomatic
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
End Sub
 

gemini528

Board Regular
Joined
Jun 13, 2013
Messages
53
Thanks for your input…

I tested it and it performs better than the previous version. As I mentioned before, the white screen display for about 3 seconds, now it’s still about 1 second. BTW, the sorting column is wrong. It sorts column “E”, then column “C” then column “D”. The sorting should be column “D”, “B”, and “C”.

While searching the internet, I come across with the function “me.repaint” and it works fine, however, when I save the file, it has an error. I don’t know how to insert it on the code.

Again, thank you for your help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows
This should fix the column error. Repaint is not something that I've used, I'll have a look and see if I can find any information on it.
VBA Code:
Sub Sort()
    Dim rw As Long
        Application.Calculation = xlManual
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
    With Sheets("INVTRY")
        .Unprotect Password:="password"
        rw = .Cells(Rows.Count, 2).End(xlUp).Row
        With .Range("B4:F" & rw)
            .Cells.Sort Key1:=.Columns(3), Order1:=xlAscending, _
                        Key2:=.Columns(1), Order2:=xlAscending, _
                        Key3:=.Columns(2), Order3:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlYes 'comma then space then underscore then ENTER key
        End With
        .Protect Password:="password", DrawingObjects:=False, UserInterfaceOnly:=True, Contents:=True, _
                           Scenarios:=True, AllowFiltering:=True, _
                           AllowFormattingCells:=True, AllowSorting:=True
    End With
        Application.Calculation = xlAutomatic
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,255
Messages
5,546,794
Members
410,758
Latest member
Papers
Top