Selecting a variable number of columns

[Albathien]

I have a 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
    Sheets("Cost Savings").Activate
    ActiveSheet.Range("C:N").EntireColumn.Hidden = False
[COLOR=#ff0000]    ActiveSheet.Range(Cells(1, i + 2), Cells(1, 14)).EntireColumn.Hidden = True[/COLOR]
End If
End Sub

On Sheet1 - I have 12 columns "C to N". When I change the value of C34 on sheet2, then it should hide the 'remaining' number of columns on sheet1. i.e if I enter "3" into C34 on Sheet2, then columns C,D and E should remain visible, columns F - N should be hidden on Sheet1.

I am getting error code '1004': Application-defined or object-defined error. for the red line of code.

I've tried this code without the variable and writing the variable to a cell in the target sheet, so that isn't an issue. It must be something to do with the range command.

These are some of the alternatives I've tried

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

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

 

[VoG]

Try

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

 

[Peter_SSs]

Welcome to the MrExcel board!

The reason your code was not working is that because it is placed in a worksheet module, any unqualified range references will relate to that sheet, not the active sheet. You could fix that as follows.

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

Note also that I changed the blue value. This will be needed if your written desription is correct about how many columns should remain visible.

Here's another way with, to me, simpler logic.

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

 

[Albathien]

Try


Speedy reply and exactly what I needed

This works perfect - thank you very much

 

[Albathien]

Hi Peter,

Thank you for the welcome, and especially for the explanation. I thought the Activesheet.Range() was enough of a qualifier, particularly because the previous line worked, I didn't realise I had to qualify the cell refferences too.

This has been bugging me for some time - so I really appreciate the help.

I caught the 3 as soon as I had some working code - I have apparently forgotten how to count :s

Both of your examples, as well as VoG's worked great.

Your second example is much more elegant, so I'll just be stealing that.