Hiding Columns with a Macro

[beanie3105]

Hello

I am currently creating management account spreadsheets for next year, I would like to show rolling monthly trends, but then hide the irrelevant sheets.

For example, in period 1 starts in column E and 12 is column P, so when it's period 1 I would want F-P hidden, when it's period 2 I want G-P hidden and so on.

Is there a way to do this with a macro which pulls from the period cell (B4)and automatically hides the columns I don't need

Thanks in advance

 

[AlanY]

try this

Sub Current()
    Columns("E:P").EntireColumn.Hidden = True
    Columns(Application.Match(Range("B1"), Range("E1:P1"), 0) + 4).EntireColumn.Hidden = False
End Sub

 

[AlanY]

just realised that this is properly what you're after

Sub Current()
    Range("E:P").EntireColumn.Hidden = True
    Range(Columns(5), Columns(Application.Match(Range("B1"), Range("E1:P1"), 0) + 4)).EntireColumn.Hidden = False
End Sub

 

[My Aswer Is This]

Try this:

Sub Hide_Me()
Dim ans As Long
Columns.Hidden = False
ans = Range("B4").Value + 5
ActiveSheet.Range(Cells(1, ans), Cells(1, 16)).EntireColumn.Hidden = True
End Sub

OK

 

[beanie3105]

Hello

That has worked! Thank you so much! I don't suppose I could ask an extra favour - do you know if it's possible to get it to run automatically when the period changes at all instead of having to run the macro?

Thank you!!

 

[My Aswer Is This]

There are more then one script provided. Which one do you want to use?

Hello

That has worked! Thank you so much! I don't suppose I could ask an extra favour - do you know if it's possible to get it to run automatically when the period changes at all instead of having to run the macro?

Thank you!!

 

[AlanY]

try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Range("E:P").EntireColumn.Hidden = True
    Range(Columns(5), Columns(Application.Match(Range("B1"), Range("E1:P1"), 0) + 4)).EntireColumn.Hidden = False
End Sub

 

[My Aswer Is This]

Try this:

This script will run when you enter a value into Range "B4"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
ActiveSheet.Range(Cells(1, 5), Cells(1, 16)).EntireColumn.Hidden = False
If Application.WorksheetFunction.IsNumber(Range("B4")) Then
ans = Range("B4").Value + 5
ActiveSheet.Range(Cells(1, ans), Cells(1, 16)).EntireColumn.Hidden = True
Else
MsgBox "You must enter a proper number"
End If
End If
End Sub

OK

 

[beanie3105]

Hello

The below works when I run it, however the latest post (by AlanY) doesn't work, when I update the macro and come out it disappears? Not sure if I am doing something wrong. The below is good, but would be great if it could do it automatically without me having to run it.

Thanks

Sub Current()
Range("E:P").EntireColumn.Hidden = True
Range(Columns(5), Columns(Application.Match(Range("B1"), Range("E1:P1"), 0) + 4)).EntireColumn.Hidden = False
End Sub

 

[My Aswer Is This]

Did you try the script in post #8?