Hide columns based on user input-CodePudding

Im trying to setup a planning for 20 employees and 2 years. In here I want to hide the weeks that already passed in the input.

Down below is how far I got. After testing it seems to work till the "All" part but can't figure the last part out. With the inputbox I want that the person is able to enter the value based on the first row, 2023 week 1 (20231) till 2024 week 52 (202452).

My goal is that if someone enters the value of 202336 it should show 2023 week 36 and later and it hides the previous weeks.

Is someone able to help me? Thanks in advance!

Sub Hidepastweeks()
    Dim myValue As Variant
    myValue = InputBox("Weeks visible from week:", "Visable weeks")
    Dim c As Range
    
    Range("A2").Value = myValue
    With Range("G1:DF1")
        Application.ScreenUpdating = False
        .EntireColumn.Hidden = (myValue <> "All")
        If myValue <> "All" Then
            For Each c In Range("G1:DF1").Cells
                If c.Value < "myValue" Then
                    c.EntireColumn.Hidden = True
                End If
            Next
        End If
        Application.ScreenUpdating = True
    End With
End Sub

I Tried different sites but they all seem to have an equal to formula instead of lesser then.

CodePudding user response：

It would probbably work as is if you'd take away the quotations around myValue at

If c.Value < "myValue" Then

Are the weeks written the same as you're requesting from the user input, e.g. 202336 is in CG1 (or whatever)? If not, you'll have to use adjust to be able to compare with the myValue.

CodePudding user response：

Hide Columns Before Match (`Application.Match`)

Sub HidePastWeeks()
    
    Dim myValue As Variant
    myValue = InputBox("Weeks visible from week:", "Visible weeks")
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    ws.Range("A2").Value = myValue
    
    Dim srg As Range: Set srg = ws.Range("G1:DF1")
    
    Application.ScreenUpdating = False
        
    ' allow case-insensitivity with 'vbTextCompare' i.e. 'all = ALL'.
    srg.EntireColumn.Hidden = StrComp(myValue, "All", vbTextCompare) <> 0
    
    Dim cIndex As Variant ' could be an error value
    
    If myValue <> "All" Then
        cIndex = Application.Match(myValue, srg, 0)
        If IsNumeric(cIndex) Then ' show
            srg.Resize(, srg.Columns.Count - cIndex   1) _
                .Offset(, cIndex - 1).EntireColumn.Hidden = False
        'Else ' show none: the perfect spot for an annoying message box
        End If
    'Else ' show all
    End If
    
    Application.ScreenUpdating = True

End Sub

Hide Columns Before Match (Application.Match)

Hide Columns Before Match (`Application.Match`)