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.

 

[alansidman]

It is possible, however, it would help if you uploaded your data as an XL2BB as we cannot manipulate and test data in a picture.

 

[EXCEL MAX]

You can try this...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
    Dim vCH As Integer, vF1 As Range, vF2 As Range, vC1 As Integer, vC2 As Integer
   
    Application.ScreenUpdating = False
    With ActiveSheet
        For vCH = 3 To .UsedRange.Columns.Count
            Columns(vCH).Hidden = False
        Next vCH
        Set vF1 = Application.Range("C4", Cells(4, _
                    .UsedRange.Columns.Count)).Find([B1], , xlFormulas)
        Set vF2 = Application.Range("C4", Cells(4, _
                    .UsedRange.Columns.Count)).Find([B2], , xlFormulas)
        If Not vF1 Is Nothing And Not vF2 Is Nothing Then
            vC1 = vF1.Column
            vC2 = vF2.Column
            For vCH = 3 To .UsedRange.Columns.Count
                If Not vCH = vC1 And Not vCH = vC2 Then _
                    Columns(vCH).Hidden = True
            Next vCH
        End If
    End With
   
End Sub

 

[igold]

How about this:

Sub ShowColRange()

    Dim sD As Date, eD As Date
    Dim sCol As Range
    Dim sAdd As String, sAdd2 As String
    Dim Da As Long, lcol As Long
    
    sD = Range("B1")
    eD = Range("B2")
    Da = eD - sD
    lcol = Cells(4, Columns.Count).End(xlToLeft).Column
    Range(Cells(4, 3), Cells(4, lcol)).EntireColumn.Hidden = True
    Set sCol = Range(Cells(4, 3), Cells(4, lcol)).Find(what:=sD)
    sAdd = sCol.Address
    sAdd2 = Range(sAdd).Offset(0, Da).Address
    Range(sAdd, sAdd2).EntireColumn.Hidden = False
    
End Sub

 

[robocop]

How about this:

Sub ShowColRange()

    Dim sD As Date, eD As Date
    Dim sCol As Range
    Dim sAdd As String, sAdd2 As String
    Dim Da As Long, lcol As Long
   
    sD = Range("B1")
    eD = Range("B2")
    Da = eD - sD
    lcol = Cells(4, Columns.Count).End(xlToLeft).Column
    Range(Cells(4, 3), Cells(4, lcol)).EntireColumn.Hidden = True
    Set sCol = Range(Cells(4, 3), Cells(4, lcol)).Find(what:=sD)
    sAdd = sCol.Address
    sAdd2 = Range(sAdd).Offset(0, Da).Address
    Range(sAdd, sAdd2).EntireColumn.Hidden = False
   
End Sub

Hello igold
Thank you for responding.
This is definitely on the right track. This code is doing what i need however when i do enter new set of start/end dates, it doesnt automatically update untill i run the macro.
I'm i missing anything?

 

[robocop]

You can try this...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
    Dim vCH As Integer, vF1 As Range, vF2 As Range, vC1 As Integer, vC2 As Integer
  
    Application.ScreenUpdating = False
    With ActiveSheet
        For vCH = 3 To .UsedRange.Columns.Count
            Columns(vCH).Hidden = False
        Next vCH
        Set vF1 = Application.Range("C4", Cells(4, _
                    .UsedRange.Columns.Count)).Find([B1], , xlFormulas)
        Set vF2 = Application.Range("C4", Cells(4, _
                    .UsedRange.Columns.Count)).Find([B2], , xlFormulas)
        If Not vF1 Is Nothing And Not vF2 Is Nothing Then
            vC1 = vF1.Column
            vC2 = vF2.Column
            For vCH = 3 To .UsedRange.Columns.Count
                If Not vCH = vC1 And Not vCH = vC2 Then _
                    Columns(vCH).Hidden = True
            Next vCH
        End If
    End With
  
End Sub

@ Excel Max - thanks for replying. Nothing happens when i run this code.

 

[robocop]

It is possible, however, it would help if you uploaded your data as an XL2BB as we cannot manipulate and test data in a picture.

@ alansidman - thanks for the tip. I will try to download XL2BB and use when posting moving forward.

 

[igold]

No, I thought you would attach it to a Command Button. There is plenty of real estate there. Perhaps in the C1:D2 area. Enter your dates and click.

 

[robocop]

No, I thought you would attach it to a Command Button. There is plenty of real estate there. Perhaps in the C1:D2 area. Enter your dates and click.

Sorry, doing that didn't work neither.

 

[igold]

If you want something automatic you could put this code into the worksheet module that the data exists on. The code will run when you enter the end date...

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Target = Range("B2") Then Exit Sub
    
    Dim sD As Date, eD As Date
    Dim sCol As Range
    Dim sAdd As String, sAdd2 As String
    Dim Da As Long, lcol As Long
    
    sD = Range("B1")
    eD = Range("B2")
    Da = eD - sD
    lcol = Cells(4, Columns.Count).End(xlToLeft).Column
    Range(Cells(4, 3), Cells(4, lcol)).EntireColumn.Hidden = True
    Set sCol = Range(Cells(4, 3), Cells(4, lcol)).Find(what:=sD)
    sAdd = sCol.Address
    sAdd2 = Range(sAdd).Offset(0, Da).Address
    Range(sAdd, sAdd2).EntireColumn.Hidden = False
    
End Sub