VBA hide columns if

[eliozo]

I want to click a button where I need to type a date, then automatically hide all columns where the date typed is greater than the dates in Rows 11 in my workbook.Can you please help asap?Thank you so much

 

[Jeffrey Mahoney]

Please try this

Sub HideColumnsOnDate()
  
  Dim v As Variant
  Dim CelDate As Date
  Dim Def As String
  Dim iDate As Date
  Dim R As Range
  Dim Cel As Range
  Dim unhide As Range
  Dim hide As Range
  
  Def = Format(Now(), "mm/dd/yyyy")
  v = Application.InputBox("Please Enter a date", "Enter a Date", Def, Type:=1)
  If IsError(v) Then Exit Sub
  If v = "" Then Exit Sub
  iDate = Int(v)
  If IsDate(iDate) = False Then Exit Sub
  
  Set R = Range(Range("A11"), Range("ZZ11"))
  For Each Cel In R
    CelDate = Int(Cel.Value)
    If Cel.Value <> "" And Cel.Value > 0 And IsDate(CelDate) Then
      If iDate > CelDate Then
        If Not hide Is Nothing Then
          Set hide = Union(hide, Cel)
        Else
          Set hide = Cel
        End If
      Else
        If Not unhide Is Nothing Then
          Set unhide = Union(unhide, Cel)
        Else
          Set unhide = Cel
        End If
      End If
    End If
  Next Cel
  If Not hide Is Nothing Then
    hide.EntireColumn.Hidden = True
  End If
  If Not unhide Is Nothing Then
    unhide.EntireColumn.Hidden = False
  End If
  
  
  
End Sub

 

[eliozo]

CelDate = Int(Cel.Value)


It gave me an error on this line.
Thank you so much for helping me but if you can fix this bug. Plus I need a button to unhide all the columns that were hided. If possible to do it on the same button, if not it's ok another button might help too. Thank you so much for replying asap.

 

[Peter_SSs]

See how this goes. If you want to unhide all the columns, leave the input box empty and just click OK.

Sub Hide_Cols()
  Dim Dte As Date
  Dim sDte
  Dim c As Range
  
  sDte = InputBox("Enter date")
  Application.ScreenUpdating = False
  If Len(sDte) = 0 Then
    ActiveSheet.UsedRange.EntireColumn.Hidden = False
  ElseIf IsDate(sDte) Then
    ActiveSheet.UsedRange.EntireColumn.Hidden = False
    Dte = DateValue(sDte)
    For Each c In Intersect(Rows(11), ActiveSheet.UsedRange)
      If IsDate(c.Value) Then c.EntireColumn.Hidden = Dte > c.Value
    Next c
  End If
  Application.ScreenUpdating = True
End Sub

 

[eliozo]

Thank you SOOO much! it perfectly worked

 

[Peter_SSs]

Thank you SOOO much! it perfectly worked

You are very welcome. Thanks for letting us know.

 

[eliozo]

I still need your favor Peter_SSs if you could help me in this one i appreciate it:
https://www.mrexcel.com/forum/excel...ut-data-but-keeping-formulas.html#post5104434