Hiding Columns

R

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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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
 
Upvote 0
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:
Upvote 0

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
 
Upvote 0
Welcome to the MrExcel board!

Rockey_01 said:

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
Click to expand...
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:
Upvote 0
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.
 
Upvote 0
Rockey_01 said:
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.
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Macro - Columns Are not Hidding / unhiding correctly
Replies
4
Views
142
GarethCW
G
H
  • Question
Hide Columns VBA - refusing to hide random columns
Replies
2
Views
250
hmltnangel
H
J
  • Solved
Creating a VBA toggle button to show/hide multiple adjacent and non-adjacent columns in Excel
Replies
4
Views
741
dmt32
D
P
  • Question
Help with Vba Code
Replies
3
Views
146
Coyotex3
C
H
  • Question
Hide/Unhide Columns using VBA
Replies
8
Views
361
Joe4
Joe4

Forum statistics

Threads
1,214,599
Messages
6,120,448
Members
448,966
Latest member
DannyC96

Share this page

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
Back
Top