Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to Hide Rows in Excel

    The following Excel code works OK in my workbook, but takes over half a minute to run on each worksheet. There's nearly 80 worksheets so its pretty tedious waiting for the code to finish before I switch to the next sheet to start again.

    The code works on a pre-selected range in one column and looks for zeroes and then hides that row before moving to the next row. The range (300+ rows in col A) I have pre-selected contains a formula, an IF statement, and represent zero activity on that row. But the macro only works on one sheet at a time.

    Is there a way this code can be modified to run on several pre-selected sheets?
    Or can you suggest an alternative procedure?

    Sub HideZeroAccounts()
    '

    Application.ScreenUpdating = False

    Selection.EntireRow.Hidden = False
    Dim rng As Range
    Dim r As Long
    Set rng = Selection.Columns(1)
    For r = rng.Rows.Count To 1 Step -1
    If Not rng.Cells(r) = "" And rng.Cells(r) = 0 Then
    rng.Cells(r).EntireRow.Hidden = True
    End If
    Next r
    Application.ScreenUpdating = True

    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this? It should be much faster than your method as well...
    Select the desired sheets than run the code. It works on the numbers in col A on each sheet.
    Steve

    Code:
    Option Explicit
    Sub HideZeroAccounts()
      Dim wks As Worksheet
      Dim rConst As Range
      Dim rForms As Range
      Dim rCell As Range
      Application.ScreenUpdating = False
      For Each wks In ActiveWindow.SelectedSheets
        wks.Columns(1).EntireRow.Hidden = False
        On Error Resume Next
        Set rConst = wks.Columns(1).SpecialCells(xlCellTypeConstants, xlNumbers)
        Set rForms = wks.Columns(1).SpecialCells(xlCellTypeFormulas, xlNumbers)
        On Error GoTo 0
        If Not rConst Is Nothing Then
          For Each rCell In rConst
            If rCell = 0 Then
              rCell.EntireRow.Hidden = True
            End If
          Next rCell
        End If
        If Not rForms Is Nothing Then
          For Each rCell In rForms
            If rCell = 0 Then
              rCell.EntireRow.Hidden = True
            End If
          Next rCell
        End If
      Next wks
      Application.ScreenUpdating = True
    End Sub

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Arcturus16a (2011-03-24)

  4. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Steve. Works great. Tested four sheets ran in fifty seconds instead of one sheet in about forty seconds. At least now I can get another cup of coffee while it runs. (BTW. Forgot to mention that I have already turned Calc to Manual)

    What does Option Explicit do?

  5. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Option Explicit
    This forces you to explicitly pre-declare a variable name.
    Then if the code encounters an unknown variable name at runtime it halts. This ensures that you do not inadvertently store values in unexpected places that gives rise to frustrating results !!
    IT is easy to mistype a variable name and .... hair pulling time.
    Cheers
    G

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Is there a reason you can't use an autofilter?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •