Hide a number of columns depends of a cell vaue
Likes Likes:  0
Results 1 to 10 of 10

Thread: Hide a number of columns depends of a cell vaue

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide a number of columns depends of a cell vaue

    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 !

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,488
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

    First off, you can use this in place of all the code that you posted...
    Code:
    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
    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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Feb 2017
    Location
    Nevada
    Posts
    286
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

    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.

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

    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.

  5. #5
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

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

  6. #6
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

    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.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,488
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

    Quote Originally Posted by otrava18 View Post
    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)...
    Code:
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

    Thanks guys,

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

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

    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 ?

  10. #10
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide a number of columns depends of a cell vaue

    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 ?
    Last edited by otrava18; Feb 15th, 2018 at 04:07 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •