Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Sign in to follow this  
ZiyaSepp

Auto Hide Columns Based on Selection

Question

All, 

I'm looking for some help on a VBA code! I have a spreadsheet which holds data for products we sell (columns A to AY) 

Product Data Data 1 Data 2 Data 3 Data 4 Data 5
1 x x x x x
2 x x x x x
3 x x x x x
4 x x x x x
5 x x x x x
6 x x x x x

I have 10 products in total, each of which has 5 columns of matching data. So Product 1 is related to columns B:F, Product 2 is related to columns G:K, and so on. Is it possible to get VBA to hide the columns not applicable when a Product in column A is selected? Also can it highlight the selected Product in blue?

I know this has something to do with WorkSheet Change Events? But i'm not too familiar with it... any help would be great!

Thanks

Z

Share this post


Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0

hehe yes, thankfully each batch size of data is the same so we can avoid having a SELECT CASE or IF statement ;)

 

Sorry, my mistake! Totally forgot about the coloured cell. I've added a few more lines of code in the main IF statement to cover that. Hopefully light blue fits your colour scheme.

Code:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Source: www.ExcelWTF.com
'Purpose: Auto-hide columns based on cell selection

 

'---- Variables
Dim r As Long

 

'---- Code
'Exit Sub if more than one cell is selected or data is deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

'If selection is in the 'Product Range'
If Not Intersect(Target, Range("A2:A11")) Is Nothing Then
    r = ActiveCell.Row 'Determine the current row number
    ActiveSheet.Range("A2:A11").Interior.ColorIndex = 0 ' Clear Colours
    ActiveSheet.Range("A" & r).Interior.ColorIndex = 37 'Colour the correct cell
    Columns("B:AY").EntireColumn.Hidden = True 'Hide all columns
    'The code below works out the Starting column number for each batch of data
    If r > 2 Then r = 2 + (r - 2) * 5
    'Unhide the data in column r and the next 4 columns
    Range(Columns(r), Columns(r + 4)).EntireColumn.Hidden = False
End If
    
End Sub

Thanks,

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Hi Ziya, 

Thanks for the question, it turned out to be an interesting one to solve! I see you've read something about Worksheet_Change events, you're correct in thinking that's what we need to use!

Please post the following code in the SHEET CODE (Not a module or 'ThisWorkbook') specific to the sheet you wish to run the marco.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Source: www.ExcelWTF.com
'Purpose: Auto-hide columns based on cell selection

'---- Variables
Dim r As Long

'---- Code
'Exit Sub if more than one cell is selected or data is deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

'If selection is in the 'Product Range'
If Not Intersect(Target, Range("A2:A11")) Is Nothing Then
    r = ActiveCell.Row 'Determine the current row number
    Columns("B:AY").EntireColumn.Hidden = True 'Hide all columns
    'The code below works out the Starting column number for each batch of data
    If r > 2 Then r = 2 + (r - 2) * 5
    'Unhide the data in column r and the next 4 columns
    Range(Columns(r), Columns(r + 4)).EntireColumn.Hidden = False
End If
    
End Sub

Let me know how you get on 

Thanks

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

o.O Wait... what!? LOL

 

I was expecting some sort of 10 level IF Statement, but that equation to find the starting column is genius... i wouldn't have thought of that!

 

Code works great! However, the selected cell doesnt appear to turn blue when I choose a product. Do I need to add conditional formatting or something?

 

Z

 

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Not a problem! It's all working great now. Thanks for adding comments to the code so I can sort of understand what is going on :$ lol

 

With the color change, if I want to change it in the future, do I just change this line:

Code:

ActiveSheet.Range("A" & r).Interior.ColorIndex = 37 'Colour the correct cell

How do I know what ColorIndex number to pick for Red, or yellow for example?

 

Thanks

Z

Share this post


Link to post
Share on other sites
  • 0

Hello Ziya, 

 

Glad it's all working now, let me know if you need any mods in the future but feel free to have a go yourself too ;)

 

Yes, you would just alter the ColorIndex property, you can find a list of all the applicable colours (and corresponding numbers) here:

http://dmcritchie.mvps.org/excel/colors.htm

 

Also, You may have noticed we have a new Best Answer system, if my last bit of code solved you problem. Have a go at clicking the checkmark next to my post :D

qysco0.png

 

Many Thanks

Caleeco

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×