VBA to display specific columns based on a header in a range...

[sadams1]

How much of a stretch would it be to take the following range (A1:E4) with the header row (A1:E1) & have code that would display specific columns based on a header value? For instance, "show only the column with 'Banana' in the header" or "show only columns with 'Apple' and 'Banana' in the header row".

I have the following but it only selects the columns & doesn't hide the others.

Sub FindAddressColumn()
Dim xRg As Range
Dim xRgUni As Range
Dim xFirstAddress As String
Dim xStr As String
On Error Resume Next
xStr = "Name"
Set xRg = Range("A1:P1").Find(xStr, , xlValues, xlWhole, , , True)
If Not xRg Is Nothing Then
xFirstAddress = xRg.Address
Do
Set xRg = Range("A1:P1").FindNext(xRg)
If xRgUni Is Nothing Then
Set xRgUni = xRg
Else
Set xRgUni = Application.Union(xRgUni, xRg)
End If
Loop While (Not xRg Is Nothing) And (xRg.Address <> xFirstAddress)
End If
xRgUni.EntireColumn.Select
End Sub

 

[sadams1]

Holy crap! got this going & keep saying, "I can't believe this!" this is AMAZING...MANY THANKS!

 

[mumps]

You are very welcome.

 

[sadams1]

HUGE APOLOGY for coming back again about this but it is incredibly useful & has become a "go to" function at our shop. One request that is consistently asked is "can a 2nd input box be added so 2 variables can be searched?" I've been messing around with it & not having any luck. If possible, would you be willing to see if adding this would be relatively painless to do? If it's too much, please say so. What I've done is added the additional field to the input box in the attached file. From my limited programming knowledge, this would be like an "AND" condition (?)
Thank you again for considering this!

z.xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

What I've done is added the additional field to the input box in the attached file.

By "input box" are you referring to the list box in the userform? Do you want to be able to select more than one column in the userform listbox and show/hide columns based on the multiple selections? Please explain in detail using a few examples.

 

[sadams1]

Sorry for not being clear...attached is a screenshot of the userform where I added a 2nd input box outlined in yellow. The last update you made was the input box under the "Filter Header by" button which allowed for searches using wildcards like "*car*" or "*age*". I've had many requests for an additional field that would allow a similar search that would allow for "*car*" AND "*age*".

 

[mumps]

Replace this line of code:

If rng Like "*" & TextBox1.Value Then

with this line:

If rng Like "*" & TextBox1.Value & "*" Or rng Like "*" & TextBox2.Value & "*" Then

 

[sadams1]

AMAZING! So incredible simple but exponentially useful. Thank you very much!

 

[mumps]

My pleasure.