Show Range of Cells based on Cell value (multiple cell values or buttons) – Excel VBA

[exaccounting]

I want to display/show columns based on cell values or button commands – VBA Eg : I am trying to compare monthly sales values for the period Jan to Dec via VBA Code, I want to show only two columns (range of columns) for two months which I am comparing among 12 months. So if I want to compare March and April, I would select March In cell A2 and April cell B2, and macros/code should show only range of cells based on these two cell values (A2= March and B2= April ) which would be March sale ranges, and April Sales ranges (column headings) and so on. I have tried to write the code as follows but I am not able to show two ranges of cells together …..

<tbody>
</tbody>

<code>Sub RevHideColumnsHR1()
Select Case Range("D4").Value
Case "January"

Range("A:B,H:AN").Select
Selection.EntireColumn.Hidden = True

End select
End sub</code>

Once it shows correct range of columns, I also want to give simple difference formula for visible columns cells only (sale values for each customer in Mar and April) the variance range of columns would be fixed but only considering visible columns/cell values for calculation purpose…

 

[pbornemeier]

I could not figure out your data layout from the info provided, so I made a simplified version for this sample code:

Option Explicit

Sub Show2MonthsAndDifference()

    'D2 = "January", .... O2="December" with data below
    'A2 = 1st Month to show
    'B2 = 2nd month to show
    
    'Q is the difference column
    
    Dim lColumnIndex As Long
    Dim l1stColumn As Long
    Dim l2ndColumn As Long
    Dim lLastRow As Long
    
    For lColumnIndex = 4 To 15
        Select Case UCase(Cells(2, lColumnIndex).Value)
        Case UCase(Range("A2").Value)
            l1stColumn = lColumnIndex
            ActiveSheet.Columns(lColumnIndex).EntireColumn.Hidden = False
        Case UCase(Range("B2").Value)
            l2ndColumn = lColumnIndex
            ActiveSheet.Columns(lColumnIndex).EntireColumn.Hidden = False
        Case Else
            ActiveSheet.Columns(lColumnIndex).EntireColumn.Hidden = True
        End Select
    Next
    
    lLastRow = Cells(Rows.Count, 4).End(xlUp).Row
    Cells(2, 17).Value = Range("A2").Value & " - " & Range("B2").Value
    With Range(Cells(3, 17), Cells(lLastRow, 17))
        .FormulaR1C1 = "=RC" & l1stColumn & "-RC" & l2ndColumn
        Application.Calculate
        DoEvents
        .Value = .Value
    End With
End Sub

 

[exaccounting]

Thanks a ton Phil

I tried your code but I could not get results as I was looking for....

I have now attached Excel workbook as example, the first sheet has single columnar monthly data and the second sheet has multiple columns monthly data, I have kept variance columns already which should always show when two months figures are compared

External Link for file download Sale Monthly Comparision VBA Data
I have already added a module and pasted code you have provided...

May you please work on attached workbook and see if you can help me with exact code I am looking for?

Many Thanks once again

Cheers
Prash