Macro for column range

[robocop]

I have a whole calendar year of inventory data. I want that every time I enter a start & end dates (ex. cells B1 & B2) it ONLY makes viewable the columns of the specified date ranges
while other columns before and after the selected dates are hidden. Is this possible? See example below. I hope it makes sense. Thank you again to all the excels masterminds out there who helps others solves problems.

 

[EXCEL MAX]

To test this code you need to do few things before.
First, insert code in the ThisWorkbook code module.
Second, code suppose that all cells with dates are formated as a date, not a text.
Third, sheet need to look as you show in the post.
Fourth, the dates you insert in the B1 and B2 need to exist in the column 4.

 

[igold]

A button should work but you can try my code in Post #10. It must be put in the correct corresponding Sheet module. So if your inventory is on Sheet1, insert this code into the Sheet1 module in the Editor. You can get there by double clicking on the correct sheet in the left hand pane of the editor.

 

[igold]

@EXCEL MAX - I just tried your code and it does not return a range of columns. It only returns the "Start Date" and the "End Date"

 

[Snakehips]

This was to be my suggestion. Contains some possibly useful, data validation?
Should default to a full set of columns if bad / incomplete entries made to B1 or B2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngB1 As Range, RngB2 As Range, RngC4 As Range
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B1:B2")) Is Nothing Then Exit Sub

Set RngB1 = Range("B1")
Set RngB2 = Range("B2")
Set RngC4 = Range("C4")

ActiveSheet.UsedRange.Columns.EntireColumn.Hidden = False

LastCol = Cells(4, Columns.Count).End(xlToLeft).Column

If Not IsDate(RngB1) Or Not IsDate(RngB2) Then Exit Sub
If RngB2 < RngB1 Then Exit Sub
If Not RngB1 >= RngC4 Or RngB1 > Cells(4, LastCol) Then Exit Sub
If Not RngB2 >= RngC4 Or RngB2 > Cells(4, LastCol) Then Exit Sub

Application.ScreenUpdating = False
Range(Cells(4, 3), Cells(4, LastCol)).Columns.EntireColumn.Hidden = True

Show1 = RngB1 - RngC4 + 3
Show2 = Show1 + RngB2 - RngB1

Range(Cells(4, Show1), Cells(4, Show2)).EntireColumn.Hidden = False

Application.ScreenUpdating = True
End Sub

 

[EXCEL MAX]

Yes, you have right.
It's because I have understand a post in that way.
I just tried you code and saw a difference.

 

[igold]

No problems ?

 

[robocop]

This was to be my suggestion. Contains some possibly useful, data validation?
Should default to a full set of columns if bad / incomplete entries made to B1 or B2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngB1 As Range, RngB2 As Range, RngC4 As Range
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B1:B2")) Is Nothing Then Exit Sub

Set RngB1 = Range("B1")
Set RngB2 = Range("B2")
Set RngC4 = Range("C4")

ActiveSheet.UsedRange.Columns.EntireColumn.Hidden = False

LastCol = Cells(4, Columns.Count).End(xlToLeft).Column

If Not IsDate(RngB1) Or Not IsDate(RngB2) Then Exit Sub
If RngB2 < RngB1 Then Exit Sub
If Not RngB1 >= RngC4 Or RngB1 > Cells(4, LastCol) Then Exit Sub
If Not RngB2 >= RngC4 Or RngB2 > Cells(4, LastCol) Then Exit Sub

Application.ScreenUpdating = False
Range(Cells(4, 3), Cells(4, LastCol)).Columns.EntireColumn.Hidden = True

Show1 = RngB1 - RngC4 + 3
Show2 = Show1 + RngB2 - RngB1

Range(Cells(4, Show1), Cells(4, Show2)).EntireColumn.Hidden = False

Application.ScreenUpdating = True
End Sub

@ Snakechips - Thank you, Thank you! This code works perfectly!