Excel VBA: Hiding columns between two given dates

Question

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

Shai Rado · Accepted Answer · 2017-01-13 08:47:10Z

3

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

answered Jan 13, 2017 at 8:47

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dgorti · Accepted Answer · 2017-01-13 08:34:29Z

2

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

answered Jan 13, 2017 at 8:34

dgorti

1,2802 gold badges15 silver badges23 bronze badges

Collectives™ on Stack Overflow

Excel VBA: Hiding columns between two given dates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related