VBA Selecting Columns depending on a cell value

Question

I am trying to make view options in an Excel table. I have the radio buttons ready, marked Sales, Contracts, Accounts and All. Each one will show/hide columns depending on which ones are relevant to the particular department. The way I want to filter which columns are relevant is by having a cell in each column (at the top) which checks if it contains s,c or a. Is the best way to use commas if two departments need to view the same column?

view options

table

Here is the code I currently have, but it requires the cells to be defined in the macro code:

Sub SalesOption_Click()
Columns("B:DD").Select
Selection.EntireColumn.Hidden = True
Application.Union(Columns("c:p"), Columns("bt:bw"), Columns("cb")).Select
Selection.EntireColumn.Hidden = False
Range("$A$1").Select
End Sub

Sub AllOption_Click()
Columns("B:DD").Select
Selection.EntireColumn.Hidden = False
Range("$A$1").Select
End Sub

Any guidance would be much appreciated.

Not a reply, but please get rid of all those `Select` and `Selection`!! http://stackoverflow.com/q/10714251/78522 — iDevlop, Dec 18 '14 at 12:22

EngJon · Accepted Answer · 2014-12-18T14:41:32.077

2

Insert this function:

Function FindCols(colVal As String)
    Columns("B:DD").EntireColumn.Hidden = False
    Dim LastCol As Integer
    Dim desigRow As Integer
    desigRow = 1 'first row will be checked for letters
    With ActiveSheet
        LastCol = .Cells(desigRow, .Columns.Count).End(xlToLeft).Column
    End With
    Dim i As Integer
    Dim rng As Range
    For i = 1 To LastCol
        Set rng = Cells(desigRow, i)
        If InStr(1, Cells(desigRow, i), colVal) = 0 Then 'If the column doesn't contain the wanted letter -> hide it
            rng.EntireColumn.Hidden = True
        End If
    Next i
End Function

This searches the columns for the right content and hides them if they are not containing it.

Now you only have to call this function from the subs of your buttons, for example:

Sub SalesOption_Click()
    FindCols "s"
End Sub

or

Sub AllOption_Click()
    Columns("B:DD").EntireColumn.Hidden = False
End Sub

The other two should be possible for yourself to create (hint: it's different in only one letter).

EDIT: In order to satisfy the wish for avoidance of .Select-Statements, I altered my code

edited Dec 18 '14 at 14:41

answered Dec 18 '14 at 12:25

EngJon

987
8
20

nice solution, minor observation move the Dim rng Range to the top, outside of your for loop – Mark Moore Dec 18 '14 at 12:43
Thanks for your answer. Where can I change the row where I put the "s"? I've tried row 1 after looking at the code, but this doesn't work. – JosephFTaylor Dec 18 '14 at 13:55
At the if-clause, the code checks for 'Cells(1,i)'. The "1" stands for the first row. So in order to change the row, alter that number. If you want row 3, then change it to 'Cells(3,i)' and dont forget to alter the line under it, too (in this case, that would be 'Set rng = Cells(3,i)') – EngJon Dec 18 '14 at 14:02
I have tried changing it to row 13 and it still doesn't work. It just hides all columns B:DD. My A column is empty if this makes any difference. – JosephFTaylor Dec 18 '14 at 14:07
When I MsgBox LastCol, it says 1. – JosephFTaylor Dec 18 '14 at 14:08
Replacing For i = 1 To LastCol to For i = 1 To 5 gets it to check all columns up to column E. – JosephFTaylor Dec 18 '14 at 14:11
Ugh ... failure on my part. Hiding all columns at the start won't do the trick. I edit my answer accordingly. That has to work. edit: edit on answer is done – EngJon Dec 18 '14 at 14:13
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/67230/discussion-between-josephftaylor-and-engjon). – JosephFTaylor Dec 18 '14 at 14:24

score 0 · Answer 2 · answered Dec 18 '14 at 13:11

Or you could use this, I've used an InputBox as I don't know how you want to assign the letter to search for.

Sub SO()

filterLet = InputBox("Enter Letter")
For i = 2 To 108
    Cells(1, i).EntireColumn.Hidden = InStr(1, Cells(1, i).Value, filterLet) = 0
Next i

End Sub

To show all just use

Columns("B:DD").EntireColumn.Hidden = False

VBA Selecting Columns depending on a cell value

2 Answers2