CODEWITHSUNDEEP
vbaexcel
JosephFTaylor
JosephFTaylor

Reputation: 99

VBA Selecting Columns depending on a cell value

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.

Upvotes: 0

Views: 8102

Answers (2)

EngJon
EngJon

Reputation: 997

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

Upvotes: 2

SierraOscar
SierraOscar

Reputation: 17647

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

Upvotes: 0

Related Questions