CODEWITHSUNDEEP
vbaexcel
Roland Blomqvist
Roland Blomqvist

Reputation: 13

Excel VBA: Hiding columns between two given dates

I'm working on a sheet listing days throughout the year. The rows will be populated manually by different users and the purpose is to log these values throughout a whole year. Columns are specific to each day of the year, and therefore a lot of scrolling is required to find a specific range.

What i want to achieve is to have two cells that can be filled with two dates, a start and end date, and when these are entered all the other columns are hidden from view.

I've found a method to hide columns before a given date in one cell, but id like some help to incorporate a way to also hide columns after a given date in another cell. In this case cell E35

The VBA code for the method so far is:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160725
    Dim xCell As Range
    If Target.Address <> Range("E34").Address Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In Range("H1:NG1")
        xCell.EntireColumn.Hidden = (xCell.Value < Target.Value)
    Next
    Application.ScreenUpdating = True
End Sub

A reference picture here: Example

Thank you in advance // R

Upvotes: 1

Views: 1265

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

Following-up on @dgorti answer, in future cases, when you want to monitor the worksheet for multiple ranges, you can use:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E34:E35")) Is Nothing Then
  ' perform your code hewe

Else
    ' you can put your Exit Sub here
End If

End Sub

Upvotes: 3

dgorti
dgorti

Reputation: 1240

Private Sub Worksheet_Change(ByVal Target As Range)
 'Updateby Extendoffice 20160725
  Dim xCell As Range
  If Target.Address <> Range("E34").Address AND Target.Address <> Range("E35").Address Then Exit Sub
  Application.ScreenUpdating = False
  For Each xCell In Range("G1:NG1")
    xCell.EntireColumn.Hidden = (xCell.Value < Range("E34").Value or xCell.Value > Range("E35").Value )
  Next
  Application.ScreenUpdating = True
End Sub

Upvotes: 2

Related Questions