Hiding Columns

Rockey_01

New Member
Joined
Sep 19, 2014
Messages
21
Hi,

Is there a way i can shorten the code?
I'm trying to hide multiple columns according to the value i selected from the drop-list.
A few of my value have the same columns requirement to be hidden.

'The code works so far so good (dot line and above) when i select from the drop-down list as it hide the columns accordingly
'but it stop working without error given when i try to inculde other value for different columns

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$4" Then
Dim the_selection As String
the_selection = Sheet3.Range("B4")
If the_selection = "Football" Then
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
Else
If the_selection = "Basketball" Then
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
Else
If the_selection = "Baseball" Then
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
Else
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = False


----------------------------------------------------------------------------
Else
If the_selection = "F1" Then
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V").EntireColumn.Hidden = True
Else
If the_selection = "Daytona" Then
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V").EntireColumn.Hidden = True
Else
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V").EntireColumn.Hidden = False
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,914
You could use the Select Case construction

Code:
Select Case the_selection
    Case "Football"
        Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
    Case "Basketball
        Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
    Case "Baseball"
        Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
End Select
 

ttdk1

Board Regular
Joined
May 21, 2014
Messages
189
right before the dot line, you got

<code>Else
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = False


that's the culprit. Take that out and add only the code below last code

should look like this

If Target.Address = "$B$4" Then
Dim the_selection As String
the_selection = Sheet3.Range("B4")
If the_selection = "Football" Then
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
Else
If the_selection = "Basketball" Then
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
Else
If the_selection = "Baseball" Then
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = True
Else
If the_selection = "F1" Then
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V").EntireColumn.Hidden = True
Else
If the_selection = "Daytona" Then
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V").EntireColumn.Hidden = True
Else
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V").EntireColumn.Hidden = False

Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD").EntireColumn.Hidden = False

</code>
 
Last edited:

Rockey_01

New Member
Joined
Sep 19, 2014
Messages
21

Hi,

I got an error message below for case V-PLUS, may i know what does it mean?
Run-time error "1004":
Method 'Range' of object'_Worksheets' failed

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$4" Then
Dim the_selection As String
the_selection = Sheet3.Range("B4")

Select Case the_selection
Case "ADSL"
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK:AK,AL:AL").EntireColumn.Hidden = True
Case "ISDN-2"
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK:AK,AL:AL").EntireColumn.Hidden = True
Case "ISDN-30"
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK:AK,AL:AL").EntireColumn.Hidden = True
Case "PSTS"
Sheet3.Range("Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK:AK,AL:AL").EntireColumn.Hidden = True
Case "B-ACCESS"
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V,Z:Z,AA:AA,AB:AB,AC:AC:AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK,AK,AL:AL").EntireColumn.Hidden = True
Case "V-PLUS"
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V,Z:Z,AA:AA,AB:AB,AC:AC:AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK,AK,AL:AL").EntireColumn.Hidden = True
Case "EW"
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V,Z:Z,AA:AA,AB:AB,AC:AC:AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK,AK,AL:AL").EntireColumn.Hidden = True
Case "DLC"
Sheet3.Range("AB:AB,AC:AC,AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK:AK,AL:AL").EntireColumn.Hidden = True
Case "540 GROUP"
Sheet3.Range("AB:AB,AC:AC,AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK:AK,AL:AL").EntireColumn.Hidden = True
Case "OVERVIEW"
ActiveSheet.Cells.EntireColumn.Hidden = False
Case "ELL"
ActiveSheet.Cells.EntireColumn.Hidden = False
Case "ILL"
ActiveSheet.Cells.EntireColumn.Hidden = False
Case "IBC"
ActiveSheet.Cells.EntireColumn.Hidden = False
Case "SDS-DLC"
ActiveSheet.Cells.EntireColumn.Hidden = False
Case "SDS-ELL"
ActiveSheet.Cells.EntireColumn.Hidden = False

End Select
End If

End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Welcome to the MrExcel board!


Hi,

I got an error message below for case V-PLUS, may i know what does it mean?
Run-time error "1004":
Method 'Range' of object'_Worksheets' failed

Case "V-PLUS"
Sheet3.Range("J:J,L:L,M:M,R:R,S:S,T:T,U:U,V:V,Z:Z,AA:AA,AB:AB,AC:AC:AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK,AK,AL:AL").EntireColumn.Hidden = True
You have an error in that line (& the others near it). The red colon should be a comma.

However, you also asked about shortening the code. There are two things that immediately show up as shortening options.

1. If several of your "Case" statements do the same thing then they can be combined.

2. If columns are adjoining they can be combined into a single range.

So try something more like this
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$4" Then
    Dim the_selection As String
    
    the_selection = Sheet3.Range("B4")
    
    Select Case the_selection
      Case "ADSL", "ISDN-2", "ISDN-30", "PSTS"
        Sheet3.Range("Z:AE,AH:AL").EntireColumn.Hidden = True
      Case "B-ACCESS", "V-PLUS", "EW"
        Sheet3.Range("J:J,L:M,R:V,Z:AE,AH:AL").EntireColumn.Hidden = True
      Case "DLC", "540 GROUP"
        Sheet3.Range("AB:AE,AH:AL").EntireColumn.Hidden = True
      Case "OVERVIEW", "ELL", "ILL", "IBC", "SDS-DLC", "SDS-ELL"
        ActiveSheet.Cells.EntireColumn.Hidden = False
    End Select
  End If
End Sub

Do you really mean Sheet3 for some of the cases and ActiveSheet for others?

Also, when posting code, please use code tags to preserve indentation as it makes the code much easier to read/debug. See my signature block below for how to do that.
 
Last edited:

Rockey_01

New Member
Joined
Sep 19, 2014
Messages
21
Hi Peter,

Thank you so much. It work perfectly well now with your code.
The reason I'm using ActiveSheet for others is because those are the values when I selected it does not require to hide any columns.

I'll take note of the code tags in future.
 

Rockey_01

New Member
Joined
Sep 19, 2014
Messages
21

ADVERTISEMENT

Not forgetting to Thanks ttdk1 & Mikerickson!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
The reason I'm using ActiveSheet for others is because those are the values when I selected it does not require to hide any columns.
1. Yes, but isn't all of this happening on the ActiveSheet (Sheet3)?

2. With the code as is, do you realise (& want this to happen) that if you choose say "EW" then these columns will be hidden "J:J,L:M,R:V,Z:AE,AH:AL". If you then immediately choose say "ADSL" columns "Z:AE,AH:AL" will be hidden, but so will columns "J:J,L:M,R:V" still be hidden from the "EW" choice as nothing in the code will have un-hidden them.

So, I'm guessing that something like this may be more useful?
Test it in another copy of your workbook.
I have assumed that this code is working on Sheet3, with cell B4 being changed manually.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$4" Then
    Dim the_selection As String
    
    the_selection = Range("B4")
    Application.ScreenUpdating = False
    Cells.EntireColumn.Hidden = False
    Select Case the_selection
      Case "ADSL", "ISDN-2", "ISDN-30", "PSTS"
        Range("Z:AE,AH:AL").EntireColumn.Hidden = True
      Case "B-ACCESS", "V-PLUS", "EW"
        Range("J:J,L:M,R:V,Z:AE,AH:AL").EntireColumn.Hidden = True
      Case "DLC", "540 GROUP"
        Range("AB:AE,AH:AL").EntireColumn.Hidden = True
    End Select
    Application.ScreenUpdating = True
  End If
End Sub
 

Rockey_01

New Member
Joined
Sep 19, 2014
Messages
21
Hi Peter,

Thank you for the advice. Yes, all of this are running on the same sheet3 and with the latest code it works so much better now.

On top of that I'm also trying to archive the excel data from this sheet3 to another sheets automatically when I change the status back and forth within the worksheet.
I have 4 drop-down values status (OPEN,CLOSED,PENDING & VOID) to archive into 3 different respective sheets when selected. Only the value status will maintain in the sheet.

The status selection is in sheet3 column "A" and start from cell "A11". I found some tutorial video code online and try it but it didn't run and no error given.

Is there a similar way of doing it? Thanks in advance

Code:
'Application.EnableEvents = False
'If Target.Column = 1 And UCase(Target) = "CLOSED" Then
'Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("CLOSED_ORDER").Range("A" & Rows.Count).End(xlUp).Offset(1)
'End If
'Application.EnableEvents = True
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,388
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top