VBA help -- hide columns based upon drop down

D

dmj120

Active Member
Joined
Jan 5, 2010
Messages
310
Office Version
  1. 365
  2. 2019
  3. 2010
I tried recording a macro [twice] but selecting the 'option' in A1 (drop down: All, Pricing, Review) doesn't seem to get me anything.... :confused:
VBA Code: 
Sub Macro1()
'
' Macro2 Macro
'

'
    Columns("A:AN").Select
    Selection.EntireColumn.Hidden = False
    Range("C:D,Q:AC").Select
    Range("Q1").Activate
    Selection.EntireColumn.Hidden = True
End Sub

I am trying to dynamically hide/show columns based upon the selection is A1. Something like:
IF A1 = "All" then show all columns
OR IF A1 = "Pricing" show cols A:B, F:J, AND HIDE cols N:P, AF:AN
OR IF A1 = "Review" show cols A,C, F:J, AND HIDE cols B, N,P etc.....
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
like this ??

VBA Code: 
Sub Macro1()
Application.ScreenUpdating = False

    Dim RangeAll As Range, RangePricing As Range, RangeReview As Range
    
    Set RangeAll = Columns.EntireColumn
    Set RangePricing = Union(Range("N:P"), Range("AF:AN"))
    Set RangeReview = Union(Range("B:B"), Range("N:N"), Range("P:P"))
    
    
    If Range("A1") = "All" Then
            RangeAll.Hidden = False
        
        ElseIf Range("A1") = "Pricing" Then
            RangeAll.Hidden = False
            RangePricing.EntireColumn.Hidden = True
    
        ElseIf Range("A1") = "Review" Then
            RangeAll.Hidden = False
            RangeReview.EntireColumn.Hidden = True
    
        Else
            RangeAll.Hidden = False
    End If

End Sub
 
Upvote 0
SunnyAlv said:
like this ??

VBA Code: 
Sub Macro1()
Application.ScreenUpdating = False

    Dim RangeAll As Range, RangePricing As Range, RangeReview As Range
   
    Set RangeAll = Columns.EntireColumn
    Set RangePricing = Union(Range("N:P"), Range("AF:AN"))
    Set RangeReview = Union(Range("B:B"), Range("N:N"), Range("P:P"))
   
   
    If Range("A1") = "All" Then
            RangeAll.Hidden = False
       
        ElseIf Range("A1") = "Pricing" Then
            RangeAll.Hidden = False
            RangePricing.EntireColumn.Hidden = True
   
        ElseIf Range("A1") = "Review" Then
            RangeAll.Hidden = False
            RangeReview.EntireColumn.Hidden = True
   
        Else
            RangeAll.Hidden = False
    End If

End Sub
Click to expand...

I copied that into a module, but nothing happens when I change cell A1. I've only assigned a macro to an image - since it's referencing a specific cell, should it work just by copy/pasting, or is there something else I have to do?
 
Upvote 0
dmj120 said:
I copied that into a module, but nothing happens when I change cell A1. I've only assigned a macro to an image - since it's referencing a specific cell, should it work just by copy/pasting, or is there something else I have to do?
Click to expand...
Can you provide a picture/table of your excel form?
 
Upvote 0
SunnyAlv said:
Can you provide a picture/table of your excel form?
Click to expand...

A small snippet..
1720661826972.png
 
Upvote 0
try

VBA Code: 
Sub Macro1()
Application.ScreenUpdating = False

    Dim RangeAll As Range, RangePricing As Range, RangeReview As Range
    Dim targetcell As Range
    
    Set targetcell = Range("A1").Value
    Set RangeAll = Columns.EntireColumn
    Set RangePricing = Union(Range("N:P"), Range("AF:AN"))
    Set RangeReview = Union(Range("B:B"), Range("N:N"), Range("P:P"))
    

    If targetcell = "All" Then

            RangeAll.Hidden = False

        ElseIf targetcell = "Pricing" Then

            RangeAll.Hidden = False
            RangePricing.EntireColumn.Hidden = True

        ElseIf targetcell = "Review" Then

            RangeAll.Hidden = False
            RangeReview.EntireColumn.Hidden = True

        Else

            RangeAll.Hidden = False

    End If

End Sub
 
Upvote 0
SunnyAlv said:
try

VBA Code: 
Sub Macro1()
Application.ScreenUpdating = False

    Dim RangeAll As Range, RangePricing As Range, RangeReview As Range
    Dim targetcell As Range
   
    Set targetcell = Range("A1").Value
    Set RangeAll = Columns.EntireColumn
    Set RangePricing = Union(Range("N:P"), Range("AF:AN"))
    Set RangeReview = Union(Range("B:B"), Range("N:N"), Range("P:P"))
   

    If targetcell = "All" Then

            RangeAll.Hidden = False

        ElseIf targetcell = "Pricing" Then

            RangeAll.Hidden = False
            RangePricing.EntireColumn.Hidden = True

        ElseIf targetcell = "Review" Then

            RangeAll.Hidden = False
            RangeReview.EntireColumn.Hidden = True

        Else

            RangeAll.Hidden = False

    End If

End Sub
Click to expand...
I get an error
1720662665857.png
 
Upvote 0
sorry I forgot to move it

VBA Code: 
Sub Macro1()
Application.ScreenUpdating = False

    Dim RangeAll As Range, RangePricing As Range, RangeReview As Range
    Dim targetcell As Range
    
    Set targetcell = Range("A1")
    Set RangeAll = Columns.EntireColumn
    Set RangePricing = Union(Range("N:P"), Range("AF:AN"))
    Set RangeReview = Union(Range("B:B"), Range("N:N"), Range("P:P"))
    

    If targetcell.Value = "All" Then

            RangeAll.Hidden = False

        ElseIf targetcell.Value = "Pricing" Then

            RangeAll.Hidden = False
            RangePricing.EntireColumn.Hidden = True

        ElseIf targetcell.Value = "Review" Then

            RangeAll.Hidden = False
            RangeReview.EntireColumn.Hidden = True

        Else

            RangeAll.Hidden = False

    End If

End Sub
 
Upvote 0
SunnyAlv said:
sorry I forgot to move it

VBA Code: 
Sub Macro1()
Application.ScreenUpdating = False

    Dim RangeAll As Range, RangePricing As Range, RangeReview As Range
    Dim targetcell As Range
   
    Set targetcell = Range("A1")
    Set RangeAll = Columns.EntireColumn
    Set RangePricing = Union(Range("N:P"), Range("AF:AN"))
    Set RangeReview = Union(Range("B:B"), Range("N:N"), Range("P:P"))
   

    If targetcell.Value = "All" Then

            RangeAll.Hidden = False

        ElseIf targetcell.Value = "Pricing" Then

            RangeAll.Hidden = False
            RangePricing.EntireColumn.Hidden = True

        ElseIf targetcell.Value = "Review" Then

            RangeAll.Hidden = False
            RangeReview.EntireColumn.Hidden = True

        Else

            RangeAll.Hidden = False

    End If

End Sub
Click to expand...

Hmm.... I updated the targetcell to A1 -- A11 was in the IF ELSEIF - still 424 error

1720663351545.png
 
Upvote 0
SunnyAlv said:
sorry I forgot to move it

VBA Code: 
Sub Macro1()
Application.ScreenUpdating = False

    Dim RangeAll As Range, RangePricing As Range, RangeReview As Range
    Dim targetcell As Range
   
    Set targetcell = Range("A1")
    Set RangeAll = Columns.EntireColumn
    Set RangePricing = Union(Range("N:P"), Range("AF:AN"))
    Set RangeReview = Union(Range("B:B"), Range("N:N"), Range("P:P"))
   

    If targetcell.Value = "All" Then

            RangeAll.Hidden = False

        ElseIf targetcell.Value = "Pricing" Then

            RangeAll.Hidden = False
            RangePricing.EntireColumn.Hidden = True

        ElseIf targetcell.Value = "Review" Then

            RangeAll.Hidden = False
            RangeReview.EntireColumn.Hidden = True

        Else

            RangeAll.Hidden = False

    End If

End Sub
Click to expand...
sorry to use this one please @dmj120
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Hide/Unhide Columns, different/multiple sheets
2 3
Replies
20
Views
556
Minx_Minxy
M
M
  • Question Question
VBA Macro - hides columns based on a single cell which is limited by data validation
Replies
4
Views
355
macca_18380
M
N
  • Question Question
EXCEL VBA COMBO BOX/DROP DOWN LIST
Replies
1
Views
205
Peter_SSs
Peter_SSs
E
  • Question Question
Help - Existing VBA routine to hide one column hides three?
Replies
4
Views
2K
EssKayKay
E
M
  • Question Question
Hide Rows based on cell with drop down values
Replies
1
Views
1K
kevin9999
K

Forum statistics

Threads
1,224,813
Messages
6,181,109
Members
453,021
Latest member
Justyna P

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