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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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

R
  • 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
235
rgree05
R
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
515
kevin9999
K
L
  • Solved
Vba looping thru cells and unhiding column based on value
Replies
8
Views
110
Joe4
Joe4
A
  • Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
204
Ark68
A
B
  • Locked
  • Question
Multiply VBA Macro for hiding and unhiding rows
Replies
4
Views
391
RoryA
RoryA

Forum statistics

Threads
1,215,633
Messages
6,125,925
Members
449,274
Latest member
mrcsbenson

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