VBA To hide columns based on a value in dropdown

[Corleone]

Hi
I would like add the function into a spreadsheet whereby it hides columns based on the value selected in a dropdown

the dropdown is situated in "Cover Sheet" cell C2

The sheet with the columns in is called "DATA"

The Columns start from E >> ZZ and are numbered from 0 > 697

i would like the spreadsheet to hide all columns with a value higher than that selected in the dropdown in C2 on the cover sheet

whether this can be done with our without using code im not sure
Thanks

 

[mumps]

Place this macro in the worksheet code module for sheet "Cover Sheet". Make a selection in C2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Sheets("DATA").Cells.EntireColumn.Hidden = False
    Sheets("DATA").Range(Sheets("DATA").Cells(1, Target + 6), Sheets("DATA").Cells(1, 697)).EntireColumn.Hidden = True
    Application.ScreenUpdating = True
End Sub

 

[Corleone]

My apologies Mumps , im getting my spreadsheets mixed up!!!!

The cell on the "Cover Sheet" is not a drop down after all (and its C7) The value in the cell changes automatically based on how many shifts the user needs (its a countif formula)
this is based on how many columns are filled in in a table below it.

I need the code to be incorporated within an existing macro if possible.

The rest of the info regarding the "DATA" sheet is correct (my apologies again..)

 

[mumps]

If the value in C7 is the result of a formula, then we can't use a Worksheet_Change macro because it won't be triggered by a change in a cell that is the result of a formula. The change in the cell must be made manually. In your case then, you would have to run the following macro manually after each change in C7.

Sub HideCols()
    Application.ScreenUpdating = False
    Dim startNum As Long
    startNum = Sheets("Cover Sheet").Range("C7").Value
    Sheets("DATA").Cells.EntireColumn.Hidden = False
    Sheets("DATA").Range(Sheets("DATA").Cells(1, startNum + 6), Sheets("DATA").Cells(1, 13)).EntireColumn.Hidden = True
    Application.ScreenUpdating = True
End Sub

The macro assumes that the result of the formula in C7 will be a number.

 

[Corleone]

Thanks for the response - it works to a point in that it hides some columns however i have run the macro and the value in cell C7 is 61 and its only hiding columns between column 8 and 63 and leaving the others open
the columns in the data sheet are numbered in row 1 (starting from column E)

Cheers

Edit

Cracked it by changing the 13 to 700 to cover the full range of columns

thanks for all your help

 

[mumps]

You are very welcome. My apologies. When I was testing the macro, I was using fewer columns and forgot to change the 13.