Solved Hide a number of columns based on a variable

Microsoft Excel 2010 - complete product...
July 10, 2012 at 12:39:02
Specs: Windows 7
I have a cell value on one sheet that when I change it, I would like it to hide a number of
columns on another sheet.
This is what I have so far

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer

If Target.Address = "$C$34" Then
    i = Target.Value
    Worksheets("Cost Savings").Activate
    Worksheets("Cost Savings").Range("C:N").EntireColumn.Hidden = False
    Worksheets("Cost Savings").Range(Cells(i - 3, 1), Cells(14, 1)).EntireColumn.Hidden = True
End If
End Sub

Worksheets("Cost Savings").Range(Cells(i - 3, 1), Cells(14, 1)).EntireColumn.Hidden = True

is giving error code '1004': Application-defined or object-defined error.

I have 12 columns "C to N" when I change the value of C34, then it should hide the 'remaining' number of columns (on another sheet). i.e if I enter "3" into C34, then columns C,D and E should remain visible, columns F - N should be hidden.

I'ld appreciate any help anyone could offer on this.
Thanks in advance


See More: Hide a number of columns based on a variable

Report •


#1
July 10, 2012 at 13:16:00
✔ Best Answer
You are aware that the syntax for the Cells property is as follows, right?

Cells(Row, Column)

It looks to me like you are only referencing Column A (1) in your code.

In addition, if i-3 evaluates to 0 or less, your code will send up that error.

If you enter 3 in C34, you'll be referencing Cells(0,1) which won't work since there in no Row 0.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 11, 2012 at 01:46:57
Hi DD,

Yeah I had row-column the wrong way around AND it should be i+2

Have changed both these now so that line looks like this

Worksheets("Cost Savings").Range(Cells(1, i + 2), Cells(1, 14)).EntireColumn.Hidden = True

Still giving the same error though :(

Range("F:N") works
Range(Cells(1, 6), Cells(1, 14)) doesn't work
Range(Columns(6), Columns(14)) doesn't work

I've tried copying and pasting several different examples from the internet, none that use column indexes instead of letters seem to work.

I'm beginning to think I need to reinstall and update Office 2010 :(


Thanks for your input


Report •

#3
July 14, 2012 at 07:54:31
I have been given a solution to this problem elsewhere

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer

If Target.Address = "$C$34" Then
    i = Target.Value
    Sheets("Cost Savings").Activate
    ActiveSheet.Range("C:N").EntireColumn.Hidden = False
    ActiveSheet.Range(ActiveSheet.Cells(1, i + 3), ActiveSheet.Cells(1, 14)).EntireColumn.Hidden = True
End If
End Sub

And a more elegant suggestion

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer

If Target.Address = "$C$34" Then
    i = Target.Value
    Sheets("Cost Savings").Activate
    ActiveSheet.Range("C:N").EntireColumn.Hidden = True
    ActiveSheet.Columns("C").Resize(, i).Hidden = False
End If
End Sub

Both from Peter_SSs on MrExcel

also

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Target.Address = "$C$34" Then
    i = Target.Value
    With Sheets("Cost Savings")
        .Range("C:N").EntireColumn.Hidden = False
        .Range(.Cells(1, i + 2), .Cells(1, 14)).EntireColumn.Hidden = True
    End With
End If
End Sub

From VoG on the same forum


Report •

Related Solutions


Ask Question