Hide a number of columns depends of a cell vaue

[otrava18]

Hello all ,

I have a problem with a code. I want to hide a number of columns, according to a value of a cell.
If value is ='1' unhide "E:K" and hide "E:K".
If value is ='2' unhide "E:K" and hide "F:K"
If value is ='3' unhide "E:K" and hide "G:K"
.
.
If value is ='7' unhide "E:K" and hide "K"

I tried something but i have a problem. It works if i select value fom 1 to 8 but if i make a copy and paste i receive an error (the columns are hide and unhide correctly). Run-time error '13': Type mismatch. The code is below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 And Target.Row = 3 And Target.Value = "1" Then
Application.Columns("E:K").Select
Application.Selection.EntireColumn.Hidden = False
Application.Columns("E:L").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "2" Then
Application.Columns("E:K").Select
Application.Selection.EntireColumn.Hidden = False
Application.Columns("F:K").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "3" Then
Application.Columns("E:K").Select
Application.Selection.EntireColumn.Hidden = False
Application.Columns("G:K").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "4" Then
Application.Columns("E:K").Select
Application.Selection.EntireColumn.Hidden = False
Application.Columns("H:K").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "5" Then
Application.Columns("E:K").Select
Application.Selection.EntireColumn.Hidden = False
Application.Columns("I:K").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "6" Then
Application.Columns("E:K").Select
Application.Selection.EntireColumn.Hidden = False
Application.Columns("J:K").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "7" Then
Application.Columns("E:K").Select
Application.Selection.EntireColumn.Hidden = False
Application.Columns("K:K").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "8" Then
Application.Columns("E:K").Select
Application.Selection.EntireColumn.Hidden = False

End If
End Sub


Have someone an idea about this error ? I`m noob in VBA but i like to try new things ! Thanks !

 

[Rick Rothstein]

First off, you can use this in place of all the code that you posted...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "C3" And Target.Value > 0 And Target.Value < 9 Then
    Columns("E:K").Hidden = False
    If Target.Value < 8 Then Range(Columns("D").Offset(, Target.Value), "K:K").EntireColumn.Hidden = True
  End If
End Sub[/td]
[/tr]
[/table]

Now, what do you mean by "but if i make a copy and paste i receive an error"... exactly how are you making your copy and where are you pasting it to?

 

[Coding4Fun]

Can you clarify when you say you copy and paste you get an error what specifically are you copying and pasting to where?

I tried this and it looks like you have it tied to Cell C3 to trigger the change. I manually typed in 1-8 into Cell C3 and watched as it hide and unhide the cells. This all worked as expected.

I then typed out a list of numbers from 1-8 and I copied and pasted these values into Cell C3 and it continued to work as expected.

Do you maybe have some other code in the workbook that is causing the error? I was not able to replicate the error.

 

[otrava18]

Thanks for your help. I want to try copy or delete data from a table with mouse selection and paste in the same Sheet. I receive that error. If i click Debug, the excell goes to this code second line. I will put a screenshot.

 

[otrava18]

I don`t have other code inside. I will try to create a new file and i will come back with info.

 

[otrava18]

With a blank file did the same. Try to select a part of file and paste it in the same sheet. I receive run-time error 13.

 

[Rick Rothstein]

Thanks for your help. I want to try copy or delete data from a table with mouse selection and paste in the same Sheet. I receive that error. If i click Debug, the excell goes to this code second line. I will put a screenshot.

See if this modification to the code I posted in Message #2 solves the problem (remember, this code replaces all of the code you posted early)...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "C3" Then
    If Target.Value > 0 And Target.Value < 9 Then
      Columns("E:K").Hidden = False
      If Target.Value < 8 Then Range(Columns("D").Offset(, Target.Value), "K:K").EntireColumn.Hidden = True
    End If
  End If
End Sub[/td]
[/tr]
[/table]

 

[otrava18]

Thanks guys,

Everything is ok right now. The last code is good for me and the error disappeared. Thanks Rick for your time !

 

[otrava18]

Rick ,

Can i ask you something more ? In the bottom of my table i have sums. How can i calculate a TOTAL SUM without hidden cells ?

 

[otrava18]

Hello ,

I resolve the sum problem with this

Function Sum_Visible(Cells_To_Sum As Object) Dim vTotal As Variant
Application.Volatile
vTotal = 0
For Each cell In Cells_To_Sum
If Not cell.Rows.Hidden Then
If Not cell.Columns.Hidden Then
vTotal = vTotal + cell.Value
End If
End If
Next
Sum_Visible = vTotal
End Function





But i have another challenge now. I want with Rick Rothstein code to put another cell (C4) to hide / unhide rows. C4=1 to 15 and i want to hide rows from 11 to 25. Can someone to help me with this ?