+ Reply to Thread
Results 1 to 3 of 3

Increase speed Hiding Columns

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Increase speed Hiding Columns

    I'm using a ListBox linked to a cell (KQ5) to hide/unhide columns using this macro:
    Private Sub ListBox2_Click()
      Dim cel As Range, Utilities As Range
      Dim s As String
        Application.ScreenUpdating = False
      Set Utilities = Sheets("Q1Sales").Range("S5:KO5")
      s = ListBox2.Value
      If s = "" Then
        Utilities.EntireColumn.Hidden = False
      Else
        For Each cel In Utilities
          cel.EntireColumn.Hidden = Not cel.Value = s
        Next cel
        Application.ScreenUpdating = True
      End If
      Utilities.Parent.Activate
    End Sub
    My spreadsheet is about 250 columns and 150 rows. This macro works but it is unbearably slow, like 10-15 seconds. What can I do to increase its speed down to 1-2 seconds? Thanks. David

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,927

    Re: Increase speed Hiding Columns

    Private Sub ListBox2_Click()
      Dim cel As Range, Utilities As Range
      Dim s As String
        Application.ScreenUpdating = False
      Set Utilities = Sheets("Q1Sales").Range("S5:KO5")
      s = ListBox2.Value
      If s = "" Then
        Utilities.EntireColumn.Hidden = False
      Else
        'check individual columns hidden state
        Set Utilities = Sheets("Q1Sales").Range("S5:KO5").Columns
        For Each cel In Utilities
          cel.EntireColumn.Hidden = Not cel.Value = s
        Next cel
        Application.ScreenUpdating = True
      End If
      Utilities.Parent.Activate
    End Sub
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Increase speed Hiding Columns

    Thanks Ben, I appreciate your response very much. It didn't seem to help speed-wise, however, sorry to report. I wonder if there are simply too many formulas, lookups, etc floating through this file to make this approach viable. It's a tool that has been in use for a few years by several people and it's a bit complicated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to increase the calculation speed
    By chavanalini in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-23-2013, 04:55 AM
  2. How to increase responsed speed to excel file
    By ramserp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2009, 01:29 PM
  3. How to increase speed in Excel processing
    By ROLLET in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2008, 04:11 AM
  4. [SOLVED] Increase Speed
    By SIGE_GOEVAERTS@HOTMAIL.COM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2005, 03:06 PM
  5. Increase macro speed?
    By Valeria in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-14-2005, 05:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1