+ Reply to Thread
Results 1 to 2 of 2

Hide columns with based on values

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-04-2016
    Location
    London
    MS-Off Ver
    MS Office Excel 2010/2013
    Posts
    354

    Hide columns with based on values

    Hi all,

    Can someone advice with tweaks with the code I already have .

    I am trying to hide every column or in the row and show specific month using drop down .

    Now the problem is it is not working properly when I say unhide “ALL”.

    Also it is not working properly when change it another month .

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        
        If Target.Row = 1 And Target.Column = 5 Then
            
            If Range("E1").Value = "Jan" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 1 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
    
            
            ElseIf Range("E1").Value = "Feb" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 2 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
            
            
            ElseIf Range("E1").Value = "Mar" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 3 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
            
            ElseIf Range("E1").Value = "Apr" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 4 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
            
            ElseIf Range("E1").Value = "May" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 5 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
            
            ElseIf Range("E1").Value = "Jun" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 6 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
    
            ElseIf Range("E1").Value = "Jul" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 7 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
    
            ElseIf Range("E1").Value = "Aug" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 8 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
            
            ElseIf Range("E1").Value = "Sep" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 9 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
            
            ElseIf Range("E1").Value = "Oct" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 10 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
            
            ElseIf Range("E1").Value = "Nov" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 11 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
    
            ElseIf Range("E1").Value = "Dec" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                    If Month(cell) = 12 Then
                        Columns(cell.Column).Hidden = False
                    Else
                        Columns(cell.Column).Hidden = True
                    End If
                
                Next cell
            ElseIf Range("E1").Value = "All" Then
                For Each cell In Range(Range("F4"), Range("F4").End(xlToRight))
                        Columns(cell.Column).Hidden = False
                Next cell
    
            End If
                
        End If
    
    End Sub
    .

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,652

    Re: Hide columns with based on values

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range, myMonth, x
        
        If Target.Row = 1 And Target.Column = 5 Then
            myMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
            x = Application.Match(Range("E1").Value, myMonth, 0)
            If IsNumeric(x) Then
                For Each cell In Range("F4").Resize(, 500)  '<--Adjust the size here.
                    If IsDate(cell) Then cell.EntireColumn.Hidden = Month(cell.Value) <> x
                Next cell
            End If
        End If
    End Sub

+ 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] Automatically Hide or not Hide columns, based on cell value (to the left)
    By sherylt13 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2018, 07:47 PM
  2. Hide Rows/Hide Columns Based on Value in Cell
    By brickwall823 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2015, 02:42 PM
  3. Replies: 3
    Last Post: 01-09-2014, 04:56 PM
  4. [SOLVED] Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values
    By gravy258 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-21-2013, 05:39 PM
  5. [SOLVED] Hide Columns Based on Values Referenced from Separate Worksheet
    By xkittenxx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2013, 08:35 PM
  6. VBA. Hide columns based on values in namd range.
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-19-2012, 09:15 PM
  7. Macro to hide columns based on cell values in the column to be hidden
    By JCMus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2012, 06:06 PM

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