Hide columns based on values in two seperate cells

S

SReilly

New Member
Joined
Nov 18, 2015
Messages
4
Hi everyone,

I had great success with my last query on this site (thanks to the great experts here!) and I have another VBA problem which I’m struggling with.

Basically I have data in columns E to AH (30 columns total), every ten columns is a new category for me.
Categories are as follows:- E:N is Report 1, O:X is Report 2, Y:AH is Report 3
In these reports categories I have the 10 results, E – Report 1, results 1, F – Report 1, Results 2, G- Report 1, Results 3 etc.
Cells A2 is the number of reports and A3 is the number of results.

For example if I had 2 reports but 3 results I would want columns E, F, G (from report 1), and O, P, Q( from report 2) to be shown and the rest of the columns in these report categories to be hidden and all of report 3 results to be hidden.

I can figure out how to code for values based in one cell but they don't interact well with each other as I'm telling it to show/hide cloumns at the same time
Here's my code for showing the reports columns:
Code: 
If Range("A2") = "1" Then
    Columns("O:AH").Hidden = True
    Columns("E:N").Hidden = False
        
    ElseIf Range("A2") = "2" Then
    Columns("Y:AH").Hidden = True
    Columns("E:X").Hidden = False
    
    ElseIf Range("a2") = "3" Then
    Columns("E:AH").Hidden = False
    
    ElseIf Range("A2") = "" Then
    Columns("E:AH").Hidden = False
    
    End If

And here's an example of what my hiding/unhiding the results columns looks like
Code: 
If Range("A3") = "1" Then
    Columns("F:H", "P:X", "Z:AH").Hidden = True
    Columns("E", "O", "Y").Hidden = False
        
    ElseIf Range("A3") = "2" Then
     Columns("g:H", "q:X", "aa:AH").Hidden = True
    Columns("E:f", "O:p", "Y:x").Hidden = False
    
End If

I hope I'm not going about this in all the wrong way but any pointers or tips would be greatly appreciated.
Thanks!:)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Hi Sreilly,

See if this does what you want. I am not 100% sure I understood what you wanted but see if I am close...

Be sure to test on a backup copy of your data.


Code: 
Sub HideColumns()

    Dim i As Integer, x As Integer
    Dim reps As Integer
    Dim res As Integer
    Dim rpt1(1 To 10) As Range
    Dim rpt2(1 To 10) As Range
    Dim rpt3(1 To 10) As Range
    
    Application.ScreenUpdating = False
    For i = 1 To 10
        Set rpt1(i) = Columns(4 + i) 'E-N 5-14
        Set rpt2(i) = Columns(14 + i) 'O-X 15-24
        Set rpt3(i) = Columns(24 + i) 'Y-AH 25-34
    Next
    reps = Range("A2").Value
    res = Range("A3").Value
    If reps < 1 Then
        Columns("E:AH").Hidden = False
        Application.ScreenUpdating = True
        Exit Sub
    End If
    If reps = 1 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = True
            rpt3(i).Hidden = True
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
        Next
        End If
        If reps = 2 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = False
            rpt3(i).Hidden = True
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
                rpt2(x).Hidden = True
        Next
        End If
        If reps = 3 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = False
            rpt3(i).Hidden = False
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
                rpt2(x).Hidden = True
                rpt3(x).Hidden = True
        Next
    End If
    Application.ScreenUpdating = True
End Sub


Let me know how it goes.

HTH

igold
 
Upvote 0
Based on your description, I think this code will do what you want...
Code: 
Sub HideNonResults()
  Dim X As Long
  Columns("E:AH").Hidden = True
  For X = 1 To [A2]
    Columns("E").Offset(, 10 * X - 10).Resize(, [A3]).Hidden = False
  Next
End Sub
 
Upvote 0
Hi,

I added some error correction to be more thorough...
Code: 
Sub HideColumns()

    Dim i As Integer, x As Integer
    Dim reps As Integer
    Dim res As Integer
    Dim rpt1(1 To 10) As Range
    Dim rpt2(1 To 10) As Range
    Dim rpt3(1 To 10) As Range
    
    Application.ScreenUpdating = False
    For i = 1 To 10
        Set rpt1(i) = Columns(4 + i) 'E-N 5-14
        Set rpt2(i) = Columns(14 + i) 'O-X 15-24
        Set rpt3(i) = Columns(24 + i) 'Y-AH 25-34
    Next
    reps = Range("A2").Value
    res = Range("A3").Value
    If reps < 1 Or res < 1 Then
        Columns("E:AH").Hidden = False
        Application.ScreenUpdating = True
        MsgBox "Cell A2 and Cell A3 must contain a value between 1 and 10", vbInformation, "Exit Sub"
        Exit Sub
    End If
    If reps = 1 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = True
            rpt3(i).Hidden = True
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
        Next
        End If
        If reps = 2 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = False
            rpt3(i).Hidden = True
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
                rpt2(x).Hidden = True
        Next
        End If
        If reps = 3 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = False
            rpt3(i).Hidden = False
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
                rpt2(x).Hidden = True
                rpt3(x).Hidden = True
        Next
    End If
    Application.ScreenUpdating = True
End Sub


igold
 
Upvote 0
igold said:
Hi,

I added some error correction to be more thorough...
Code: 
Sub HideColumns()

    Dim i As Integer, x As Integer
    Dim reps As Integer
    Dim res As Integer
    Dim rpt1(1 To 10) As Range
    Dim rpt2(1 To 10) As Range
    Dim rpt3(1 To 10) As Range
    
    Application.ScreenUpdating = False
    For i = 1 To 10
        Set rpt1(i) = Columns(4 + i) 'E-N 5-14
        Set rpt2(i) = Columns(14 + i) 'O-X 15-24
        Set rpt3(i) = Columns(24 + i) 'Y-AH 25-34
    Next
    reps = Range("A2").Value
    res = Range("A3").Value
    If reps < 1 Or res < 1 Then
        Columns("E:AH").Hidden = False
        Application.ScreenUpdating = True
        MsgBox "Cell A2 and Cell A3 must contain a value between 1 and 10", vbInformation, "Exit Sub"
        Exit Sub
    End If
    If reps = 1 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = True
            rpt3(i).Hidden = True
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
        Next
        End If
        If reps = 2 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = False
            rpt3(i).Hidden = True
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
                rpt2(x).Hidden = True
        Next
        End If
        If reps = 3 Then
        For i = 1 To 10
            rpt1(i).Hidden = False
            rpt2(i).Hidden = False
            rpt3(i).Hidden = False
        Next
        For x = res + 1 To 10
                rpt1(x).Hidden = True
                rpt2(x).Hidden = True
                rpt3(x).Hidden = True
        Next
    End If
    Application.ScreenUpdating = True
End Sub


igold
Click to expand...
Before you spend too much more time revising your code, take a look at what I posted in Message #3... the best I can tell, it produces the same results as your code you many fewer lines of code.
 
Upvote 0
Rick,

Clearly, I should have refreshed before I posted.

Are you sure that you have fewer lines of code, it looks pretty close to me.

Nice job! If I were the OP it would be a no brainer. I knew it was the long way around but and I know all the commands that you used. However my coding skills are not to the point that I am able to apply my current VBA vocabulary (if you will) to the most direct logic. I don't have a logic problem, it is using the right vocabulary to most efficiently get from point A to point B.

Thanks for the vocabulary lesson.

Regards,

igold
 
Upvote 0
Hi guys,

Thanks so much for your support on this, I have to admit I can follow igolds code more easily as it seems to be broken down more but I'm in awe that it can be shortened so much like you've shown Rick! I never would have gotten there on my own...
I have a love/hate relationship with getting answers here as I love reading the code others putt up and figuring out what to do but I hate that I can never think of it myself!
Again though thanks so much for your help guys, really appreciate your time on it!

Regards,

SR
 
Upvote 0
Hi SR,

Glad that we were able to help. This thread is really a great example of different ways to get to the same result. Thanks for the feedback.

igold
 
Upvote 0
Hi,
I also have a question about hiding columns based on values in separate cells, would really appreciate the help!


For example, here is what I am trying to do:

If A3 = "no", Hide columns B-Z

if B3 = 1, Hide columns F-Z
if B3=2, Hide columns M-Z
if B3=3, Hide columns Q-Z

And so on.

This seems to be very simple but I do not have much <acronym title="visual basic for applications">VBA</acronym> experience and I can only get either the ranges of columns to hide when referencing selections in A3 or B3, but not together. Can anyone here help? Thanks!

Jordan
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
405
Joe4
Joe4
S
  • Question Question
Can’t hide column using VBA
Replies
3
Views
246
Joe4
Joe4
E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
425
Joe4
Joe4
R
  • Question Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
388
rgree05
R
M
  • Question Question
VBA Macro - hides rows based on a single cell which is limited by data validation
Replies
6
Views
357
My Aswer Is This
My Aswer Is This

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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