Hiding column based on cell value

[ajitexcel]

Hi all
I am trying following code to hide/ unhide columns based on value in cell A1 but it's giving error "Function or Sub not defined"
please help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range

Set Changed = Range("A1")
If Not Intersect(Target, Changed) Is Nothing Then
Range("B:FF").EntireColumn.Hidden = False
Select Case UCase(Target.Value)
Case "40"
Range("AQ:FF").EntireColumn.Hidden = True
Case "120"
Range("CF:FF").EntireColumn.Hidden = True
Case "160"
Range("DQ:FF").EntireColumn.Hidden = True
End Select

End If

Set Changed = Nothing
End Sub

cell A1 is a dropdown list

am not able to find the reason.


Thanks

 

[Joe4]

Your code seems to work fine for me. It is not returning any errors.
Are you sure that it doesn't have a problem with some other VBA code that you may have?
If you hit debug, does it highlight a line of code that it thinks is the problem?

Note: If your entries are numbers, why are you applying "UCASE" to them? That has no effect on numbers, only text.

 

[ajitexcel]

Hi Joe
Thanks for the reply, it was highlighting line "If not intersect.....
anyways I changed the code to
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range, rTest As Range
Set rTest = Range("c1")
If rTest.Value = "" Then
Range("f:Fi").EntireColumn.Hidden = False
ElseIf rTest.Value < 41 Then
Range("F:DU").EntireColumn.Hidden = True
ElseIf rTest.Value < 121 Then
Range("f:AS").EntireColumn.Hidden = True
Range("AT:DU").EntireColumn.Hidden = False
ElseIf rTest.Value = 160 Then
Range("f:Fi").EntireColumn.Hidden = False
End If
End Sub
and it's working fine

How ever can I make this dynamic that is
if user enter's 20 then 20 columns after first 5 to hide or if he enters 50 then first 50 after 5 to hide.
or for that matter any number..

Thanks

 

[Joe4]

You can prompt the user for input using InputBox.
See: http://www.excel-easy.com/vba/examples/inputbox-function.html

You can also refer to columns numerically. So here is a little example that shows you how to do what you want:

Sub MyColumnHide()

    Dim numColsHide As Long
    
'   Request how many columns to hide
    numColsHide = InputBox("How many columns would you like to hide?")
    
'   Hide the number of columns, starting at column 6
    If numColsHide > 0 Then
        Range(Cells(1, 6), Cells(1, numColsHide + 5)).EntireColumn.Hidden = True
    End If
        
End Sub

 

[ajitexcel]

Hi Joe
This works fine one problem though, if I hide 20 columns first then enter 10 it does not unhide 10 columns. and I want to pick value from cell C1 (as it is dependent on other cell values).
Thanks again

 

[Joe4]

This works fine one problem though, if I hide 20 columns first then enter 10 it does not unhide 10 columns.

Just add a line of code at the very beginning of your code that unhides ALL columns initially, like this:

Columns.EntireColumn.Hidden = False