none
Hiding Row code running slow RRS feed

  • Question

  • Hi All......

    Excel 2010, Windows XP and Windows 7.

    I have a macro that has worked fine for years in hiding rows in a column if there is a value in a cell.  All of a sudden it has started running painfully slow, taking several seconds to process each iteration of the FOR statement.  This occurs on several different computers.

    Sub HideRowIfCompleted()

    Dim lastrow As Long, r As Long
    Sheets("WorkOrders").Select
    lastrow = Cells(Rows.Count, "i").End(xlUp).Row
    Range("a3").Select
    Application.ScreenUpdating = False
      For r = lastrow To 12 Step -1
          If Cells(r, "Au") <> "" Then
              Cells(r, "au").EntireRow.Hidden = True
          End If
      Next r
      Application.ScreenUpdating = True
    End Sub

    Any help would be much appreciated.

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Monday, February 22, 2016 5:29 PM

Answers

  • Try this macro: if it does not help, try sorting your worksheet first, based on column AU then hiding the rows as a block rather than individually.  Or just use a filter to hide those cells that are filled.

                         

    Sub SpeedMacro()
        Dim xlCalc As XlCalculation
        Dim rngA As Range
        Dim rngB As Range
        Dim rngC As Range
        Dim shtS As Worksheet
        Dim wkbkW As Workbook
        Dim i As Integer
        Dim lngR As Long
        Dim iCol As Integer

        With Application
            .ScreenUpdating = False
            xlCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayAlerts = False
        End With

    HideRowIfCompleted

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .DisplayAlerts = True
            .Calculation = xlCalc
        End With

    End Sub

    Monday, February 22, 2016 8:03 PM

All replies

  • Try this macro: if it does not help, try sorting your worksheet first, based on column AU then hiding the rows as a block rather than individually.  Or just use a filter to hide those cells that are filled.

                         

    Sub SpeedMacro()
        Dim xlCalc As XlCalculation
        Dim rngA As Range
        Dim rngB As Range
        Dim rngC As Range
        Dim shtS As Worksheet
        Dim wkbkW As Workbook
        Dim i As Integer
        Dim lngR As Long
        Dim iCol As Integer

        With Application
            .ScreenUpdating = False
            xlCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayAlerts = False
        End With

    HideRowIfCompleted

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .DisplayAlerts = True
            .Calculation = xlCalc
        End With

    End Sub

    Monday, February 22, 2016 8:03 PM
  • Many thanks Bernie.........it worked like a charm.

    Much appreciate.

    Chuck, CABgx3


    Chuck, CABGx3

    Monday, February 22, 2016 9:24 PM