Results 1 to 1 of 1

Hide Columns Loop Fails Over Multiple Sheets

Threaded View

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Hide Columns Loop Fails Over Multiple Sheets

    Hi all,

    I hope I get this right as it's my first post. I am trying to loop through a list of sheets. In each sheet I want a range of cells to be assesed and then the columns hidden based on the cell value. I can get this macro to work as long it is for one sheet. As soon as I extend the loop to look at more than one sheet, I get an error message. It's the first time I have used the Union function, something I am not used to. Any help would be most welcome, I've been looking at this for ages!

    Sub HideColumns()
        Dim ColumnsToHide As Range
        Dim SelectedCells As Range
        Dim Cell As Object
        Dim Item As Range
        Dim TeamTab As Range
        Set TeamTab = Sheets("Options").Range("D9:D10")   '"TeamTabs"
        'Set ColumnsToHide = Range("P4:AA4")
        
        For Each Item In TeamTab
            'On Error Resume Next
            With Sheets(Item.Value).Select
                    Sheets(Item.Value).Range("P4:AA4").EntireColumn.Hidden = False    'ColumnsToHide.
                    For Each Cell In Sheets(Item.Value).Range("P4:AA4")
                            If Cell.Value = 1 Then
                                If SelectedCells Is Nothing Then
                                    Set SelectedCells = Range(Cell.Address)
                                    Else: Set SelectedCells = Union(SelectedCells, Range(Cell.Address))
                                End If
                            End If
                    Next
                    If SelectedCells Is Nothing Then
                        Sheets(Item.Value).Range("P4:AA4").EntireColumn.Hidden = False
                        Else: SelectedCells.EntireColumn.Hidden = True
                    End If
            End With
        Next Item
    End Sub
    Last edited by vlady; 09-28-2012 at 07:49 PM. Reason: code tag

Thread Information

Users Browsing this Thread

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

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