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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top