+ Reply to Thread
Results 1 to 3 of 3

Macro to hide and unhide columns works in 2003 but not in 2010

Hybrid View

hhod Macro to hide and unhide... 08-28-2014, 01:41 PM
Kaper Re: Macro to hide and unhide... 09-01-2014, 03:39 AM
hhod Re: Macro to hide and unhide... 09-02-2014, 03:55 PM
  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Saskatchewan
    MS-Off Ver
    Excel 2003
    Posts
    6

    Macro to hide and unhide columns works in 2003 but not in 2010

    I'm using the following to hide blank columns when column A is autofiltered to a specific selection, unhides/rehides when the selection changes. Works great in excel 2003, but is freezing up the workbook in excel 2010. Any help is appreciated!

    Private Sub Worksheet_Calculate()
      Dim rngFlterRange As Range
      Dim rngCol   As Range
    
      If Me.AutoFilterMode Then
        If Me.AutoFilter.Filters(1).On Then
          Set rngFlterRange = Me.AutoFilter.Range
          Set rngFlterRange = rngFlterRange.Offset(1).Resize(rngFlterRange.Rows.Count - 1)
    
          Application.ScreenUpdating = False
    
          rngFlterRange.EntireColumn.Hidden = False
    
          For Each rngCol In rngFlterRange.Columns
            If Application.CountA(rngCol.SpecialCells(xlCellTypeVisible)) <> 0 Then
              If rngCol.EntireColumn.Hidden Then
                rngCol.EntireColumn.Hidden = False
              End If
            Else
              If rngCol.EntireColumn.Hidden = False Then
                rngCol.EntireColumn.Hidden = True
              End If
            End If
          Next rngCol
        Else
          Me.Columns.Hidden = False
        End If
      Else
        Me.Columns.Hidden = False
      End If
    End Sub

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,891

    Re: Macro to hide and unhide columns works in 2003 but not in 2010

    Hi hhod.
    Can you attach the sample file where freezing occurs? (of course - no delicate data).
    I made a dummy and tested it both 2003 and 2010 and works just fine - may be the problem is not in this particular event handler (Worksheet_Calculate), butr somewhere else?
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    08-30-2012
    Location
    Saskatchewan
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Macro to hide and unhide columns works in 2003 but not in 2010

    Hi,

    I've determined that if I hit F9 after I've seleced a product from column A the macro will run and everthing works how its supposed to, but I dont want to have to hit F9 everytime. Attached is a sample file, thanks for the help!
    Attached Files Attached Files

+ 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. [SOLVED] Macro works in 2010&2013 but not Excel 2003
    By Mattiac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2014, 03:12 AM
  2. Macro works fine in 2010, Crashing in 2003 - Posting problem code - Please Help?
    By RDAmidwest in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-11-2012, 04:14 PM
  3. [SOLVED] Importing CSV file in 2010. The Filesearch function works in 2003, but not in 2010.
    By Xrull in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-13-2012, 06:11 PM
  4. Hide or unhide columns with one macro
    By simonsmith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2008, 10:13 PM
  5. Hide Row macro works in 2002 but not 2003
    By Gooser555 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2005, 10:06 AM

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