Speed up a Macro

[Jools1]

Hi

Can anyone help.

I currently have a macro which takes approximately 6 minutes to run. Can anyone supply me with a code that would help speed up the process.

I have a spreadsheet that looks in column F to find any row that = "TRUE" and hides all rows that = "FALSE". In addition to this the spreadsheet also looks across row 6 to find any column that = "0" and hides that column.

Below is the code I am currently using but I hope someone will be able to help me find a new code.

I hope the above makes sense.

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Select Case CBOMonths.Value
    Case "All"
     Sheets("Matrix").Activate
ActiveSheet.PageSetup.PrintArea = Range("a1", Range("FA65536").End(xlUp)).Address
     Columns("H:FA").Select
    Selection.EntireColumn.Hidden = False
    Range("B3").Select
          Dim All As Range
    For Each All In Range("h6:cz6")
    All.EntireColumn.Hidden = (All.Value = 0)
    Next All
 
 Case "Legal"
    Sheets("Matrix").Select
     ActiveSheet.PageSetup.PrintArea = Range("H1", Range("BC65536").End(xlUp)).Address
  Columns("H:CZ").Select
    Selection.EntireColumn.Hidden = False
  Columns("BD:CZ").Select
  Selection.EntireColumn.Hidden = True
  Range("B3").Select
      Dim legal As Range
    For Each legal In Range("h6:BC6")
    legal.EntireColumn.Hidden = (legal.Value = 0)
    Next legal
 Case "Norway / Corporate"
    Sheets("Matrix").Select
     ActiveSheet.PageSetup.PrintArea = Range("BE1", Range("BM65536").End(xlUp)).Address
    Columns("H:CZ").Select
 Selection.EntireColumn.Hidden = False
 Columns("H:BB").Select
 Selection.EntireColumn.Hidden = True
  Columns("Bo:CZ").Select
  Selection.EntireColumn.Hidden = True
  Columns("BE:BM").Select
    Selection.EntireColumn.Hidden = False
    Range("B3").Select
    Dim norway As Range
    For Each norway In Range("BD6:BM6")
    norway.EntireColumn.Hidden = (norway.Value = 0)
    Next norway
 
 Case "Teesside Requirement"
   Sheets("Matrix").Select
     ActiveSheet.PageSetup.PrintArea = Range("Bo1", Range("CO65536").End(xlUp)).Address
    Columns("H:cz").Select
 Selection.EntireColumn.Hidden = False
 Columns("H:BM").Select
 Selection.EntireColumn.Hidden = True
  Columns("BO:co").Select
    Selection.EntireColumn.Hidden = False
  Columns("cq:cz").Select
  Selection.EntireColumn.Hidden = True
  Range("B3").Select
      Dim teesside As Range
    For Each teesside In Range("BO6:CO6")
    teesside.EntireColumn.Hidden = (teesside.Value = 0)
    Next teesside
 
      Case "Personal Development"
        Sheets("Matrix").Select
     ActiveSheet.PageSetup.PrintArea = Range("CQ1", Range("CZ65536").End(xlUp)).Address
          Columns("H:cz").Select
 Selection.EntireColumn.Hidden = False
  Columns("H:co").Select
    Selection.EntireColumn.Hidden = True
    Range("B3").Select
        Dim personal As Range
    For Each personal In Range("CQ6:CZ6")
    personal.EntireColumn.Hidden = (personal.Value = 0)
    Next personal
 
Case Else
End Select
   Rows("8:500").Hidden = False
 Sheets("Matrix").Select
Application.ScreenUpdating = False
Dim ucell1 As Range, urows1 As Range
rownum = ActiveSheet.UsedRange.Rows.Count
Set urows1 = Range("f8:f" & ActiveSheet.UsedRange.Rows.Count)
'change "C" to your specific column
For Each ucell1 In urows1
ucell1.EntireRow.Hidden = (ucell1 = False)
Columns("f").Hidden = True
Next ucell1
    Application.ScreenUpdating = False
 
 
Unload Me
Exit Sub
Application.ScreenUpdating = True
 
End Sub

 

[MrKowz]

First step would be to get rid of all Select/Selection statements. For example, the lines:

Columns("H:CZ").Select
    Selection.EntireColumn.Hidden = False
  Columns("BD:CZ").Select
  Selection.EntireColumn.Hidden = True

Can be consolidated to:

Columns("H:CZ").EntireColumn.Hidden = False
  Columns("BD:CZ").EntireColumn.Hidden = True

Also, along with using Application.ScreenUpdating = False/True. Add to the beginning of your code:

Application.Calculation = xlCalculationManual

and then at the end of your code

Application.Calculation = xlCalculationAutomatic

 

[Jools1]

Thanks MrKowz, I have tried your suggestions however it still doesnt seem to speed up the process.

Might have to go back to the drawing board.

 

[MrKowz]

Thanks MrKowz, I have tried your suggestions however it still doesnt seem to speed up the process.

Might have to go back to the drawing board.

Without knowing what the purpose of the macro is (what it is meant to do, and the logic behind it), especially with one that deals with a LOT of formatting such as this, it is almost impossible to really make it optimally effecient.

Good luck!

 

[Norie]

I'm afraid you might not be able to speed that code up.

The reason is because of the use of PageSetup.

I don't know the technical details but that depends in some way on the printer driver(s) you have installed.