none
How to automatic hide column base on condition RRS feed

  • Question

  • Hi everyone,

    i have a Report which is generated automatic from Sale System:

    Sale person     march          april            may       June      July

    A                      10               10              10           10        10

    B                      20               20              20            20       20

    C                       30             30                30           30       30

    i want to excel automatic hide all sale data which comes from previous months.

    Forexample.

    if the current month is July, then i want the Excel automatic hide all the Column before July, only shows July Sale

    if the current month is August, then i want the Excel automatic hide all the Column before August, only shows August Sale.

    Can you please help me how to do that?


    • Edited by Phalondon Monday, July 18, 2016 12:51 PM
    Monday, July 18, 2016 12:51 PM

Answers

  • Hi

    Two macros, one to hide and one to unhide. If your Header is not in rows 1, you'll need to change the number beside "ro", see below.

    Sub HideClmns()

    Dim mt As Integer
    Dim ro As Integer
    Dim i As Integer  

    mt = Month(Date) + 1
        ro = 1      '>>> 1 is the starting row change it to yours if it's not the first row
        For i = 2 To mt
            If Cells(ro, i) <> Cells(ro, mt) Then
                Columns(i).EntireColumn.Hidden = True
            End If
        Next
    End Sub

    Sub unhideClmns()
        Range("B:M").EntireColumn.Hidden = False
    End Sub


    Cimjet



    Monday, July 18, 2016 2:49 PM
  • Hi Phalondon,

    This is the alternative approach can also work for you.

    Sub demo()
    Dim c As Range
    Dim curmon As String
    curmon = MonthName(Month(Date), False)
    For Each c In ActiveSheet.Range("B1:M1").Cells
        If c.Value <> curmon Then
            Columns(c.Column).EntireColumn.Hidden = True
        End If
    Next
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 19, 2016 3:09 AM
    Moderator

All replies

  • Hi

    Two macros, one to hide and one to unhide. If your Header is not in rows 1, you'll need to change the number beside "ro", see below.

    Sub HideClmns()

    Dim mt As Integer
    Dim ro As Integer
    Dim i As Integer  

    mt = Month(Date) + 1
        ro = 1      '>>> 1 is the starting row change it to yours if it's not the first row
        For i = 2 To mt
            If Cells(ro, i) <> Cells(ro, mt) Then
                Columns(i).EntireColumn.Hidden = True
            End If
        Next
    End Sub

    Sub unhideClmns()
        Range("B:M").EntireColumn.Hidden = False
    End Sub


    Cimjet



    Monday, July 18, 2016 2:49 PM
  • Hi Phalondon,

    This is the alternative approach can also work for you.

    Sub demo()
    Dim c As Range
    Dim curmon As String
    curmon = MonthName(Month(Date), False)
    For Each c In ActiveSheet.Range("B1:M1").Cells
        If c.Value <> curmon Then
            Columns(c.Column).EntireColumn.Hidden = True
        End If
    Next
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 19, 2016 3:09 AM
    Moderator