Hide columns based on value in cell

K

kapilg89

New Member
Joined
Dec 12, 2012
Messages
30
Hi,

I want to hide unhide columns based on the criteria given in the cell
for eg: my cell a1 has programme life eg 5 years, so the columns for only five years should be visible and the rest of the columns, viz the rest 10 years, should be auto hidden.

Thanks in advance......
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Well, where those columns do start from?
 
Upvote 0
In worksheet module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        [COLOR=#ff0000]Cells.EntireColumn.Hidden = False[/COLOR]
        Range(Cells(1, Range("A1") + 3), Cells(1, 17)).EntireColumn.Hidden = True
    End If
End Sub
 
Last edited:
Upvote 0
Sorry for the novice question but should i insert a new module and then paste the above code of should i just paste it in the this workbook excel object
 
Upvote 0
No, the worksheet module already exists. Open VBA editor, and on the left side you will see all your sheets' modules (with names in parenthesis).
 
Upvote 0
Thanks the VB Code works fine. Except one thing that the hidden cells do not hide on entering a bigger cell value after entering a smaller value
 
Upvote 0
Since C-Q is total 15 columns, I put limiter:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        Cells.EntireColumn.Hidden = False
        If Range("A1") < 15 Then Range(Cells(1, Range("A1") + 3), Cells(1, 17)).EntireColumn.Hidden = True
    End If
End Sub
 
Last edited:
Upvote 0
Worked like a charm
Thanks buddy for the simplest of the solution!!:)

Just one more thing if i want to link the cell to a different sheet the would the code be any different?
Also i wanted a live updation of the macro. How to Achieve it?
 
Last edited:
Upvote 0
I changed code from "<=" to "<". Otherwise 15-th columns doesn't show up!
 
Upvote 0
You must log in or register to reply here.

Similar threads

David Kemp
  • Solved
Hide different columns depending on value in a cell
Replies
5
Views
100
Peter_SSs
Peter_SSs
D
  • Question
VBA code to hide/unhide based on drop-down box and tick boxes
Replies
0
Views
340
DaveOli
D
K
  • Solved
Hiding columns based on cell value
Replies
6
Views
190
Peter_SSs
Peter_SSs
S
  • Question
Hide / unhide columns without using macros / VBA
Replies
1
Views
139
JEC
JEC
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
371
kevin9999
K

Forum statistics

Threads
1,214,431
Messages
6,119,458
Members
448,899
Latest member
maplemeadows

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