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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...