Excel VBA screen blinking problem while running macro

Question

I have very long VBA code in my Excel to run a lot of processes before exporting every sheet to PDF. While running it I use:

   Application.ScreenUpdating = False
   Application.ScreenUpdating = True

The problem is that it doesn't help and my screen keeps updating anyway or at least blinking sometimes black and sometimes white. I am thinking that maybe command .Select is causing this problem? Here is one part of my code:

ActiveWorkbook.Sheets("MAIN").Select

For Each cel In Range("AX7:AX120")
If cel.Value = 1 Then
cel.Offset(0, -43).Font.Bold = True
cel.Offset(0, -44).Font.Bold = True
cel.Offset(0, -45).Font.Bold = True
cel.Offset(0, -46).Font.Bold = True
 End If
Next

lastRow = [LOOKUP(2,1/(D1:D65536<>""),ROW(D1:D65536))]

Sheets("MAIN").PageSetup.PrintArea = Sheets("MAIN").Range("A1:Z" & lastRow).Address

---------------------- EDIT ---------------------------

I have made changes, but this is not working:

   Dim printareaMain As Worksheet
   Set printareaMain = Sheets("MAIN")

        For Each cel In printareaMain.Range("AX7:AX120")
        If cel.Value < 3 Then
        cel.Offset(0, -43).Font.Bold = True
        cel.Offset(0, -44).Font.Bold = True
        cel.Offset(0, -45).Font.Bold = True
        cel.Offset(0, -46).Font.Bold = True
         End If
        Next

        'lastRow = [LOOKUP(2,1/(D1:D65536<>""),ROW(D1:D65536))]

        printareaMain.PageSetup.PrintArea = printareaMain.Range("A1:Z" & [LOOKUP(2,1/(D1:D65536<>""),ROW(D1:D65536))]).Address

Following part of the code is doing nothing:

    printareaMain.PageSetup.PrintArea = printareaMain.Range("A1:Z" & [LOOKUP(2,1/(D1:D65536<>""),ROW(D1:D65536))]).Address

See [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4088852). — Comintern, Nov 08 '18 at 16:42
There's more your code is doing that would cause it to blink - what you posted is relatively quick and shouldn't take that long. Are you doing any "heavy lifting" elsewhere in your code? But, yes, great start in wanting to remove `.Select`. — BruceWayne, Nov 08 '18 at 16:49
If you want to remove .select you can use just: ActiveWorkbook.Sheets("MAIN").Range("AX7:AX120") instead of Range("AX7:AX120") — Igelaty, Nov 08 '18 at 17:08
I have made changes after reading you suggestions but part with selecting range to the last row with text and setting print area does not work. — 10101, Nov 08 '18 at 19:01
Also, I'm assuming you're waiting to say `ScreenUpdating = True` until _after_ you finish running the macro? — ArodPonyboy678, Nov 08 '18 at 21:16

score 2 · Accepted Answer · answered Nov 08 '18 at 21:12

I think it's likely your code is doing a lot of "User" stuff - actions that emulate user actions. The comments are correct - things like Activate and Select emulate the user activating and selecting. Even setting the printArea is a user action, and can change what the user sees.

Unfortunately, there are many things that can do this. And if your code makes Excel unresponsive, this can also be a contributing factor! So the short answer is likely that "it depends on the rest of your code", as one of the comments stated.

However, there are other things you can do. You could try telling Excel to not recalculate while your macro runs:

Application.Calculation = xlCalculationManual

Just make sure to reset it after your macro is finished running.

Excel VBA screen blinking problem while running macro

1 Answers1