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

 

[smozgur]

To clarify: We mark the post as the solution that answers the question on the board, so it helps future readers easily access the solution post. It is different from "mark as closed", which is another method used in some forums. That's why I marked post #16 as the solution post in this thread.

More info:
Mark as Solution

 

[sadams1]

You keep marking your own feedback post as the solution.

Once again, I am marking the actual answer to this question. No further action is required in this question.

My apologies for bothering but now that this thread is closed, would it be possible to ask the author about a possible tweak to the solution? What I'd like to add is a search of column headers based on a common word. Thanks!

 

[smozgur]

My apologies for bothering but now that this thread is closed, would it be possible to ask the author about a possible tweak to the solution? What I'd like to add is a search of column headers based on a common word. Thanks!

This thread is not "closed". You can of course ask about a possible tweak, in fact, even someone else can post a more efficient solution, or even someone else can answer for the tweak you will be asking. That's why we don't "close" the threads but simply mark the solution post if one helped to solve the problem. There are threads in the MrExcel Message Board where people are posting better answers after years.

Being said that, if the tweak makes it a largely different question then you should start a new thread and if necessary by providing a backlink to this one.

 

[sadams1]

hello Mumps, can't thank you enough for this solution as it is used frequently. That said, would it be possible to add an additional capability that would search all the headers & filter them based on a common word? Attached are 2 screenshots, the 1st is the original userform and the 2nd is a possible updated with a "Columns containing..." button & input field beneath it. If this is too much, please let me know. Again, I greatly appreciate your time!

 

[mumps]

It is hard to work with pictures. Please upload copies of the files and post the links.

 

[sadams1]

x.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	age	day	year	yr	weather	stuff	o	p	q	r	s	Stevie lived	remodel	Stevie where/trip	Stevie work	HCC	WSU	CCH	JCCC	Becker	NAU
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
data

 

[sadams1]

Not sure if this is what you're looking for (?) I used the add-in recommended & it allows "3,000 cells" only. If you'd like the actual file, please let me know. Thanks!

 

[mumps]

It would be easier with the actual files.

 

[sadams1]

sorry for the delay! Here's the link...

x.xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

Assuming you use your second userform and:
-the "Columns containing.." command button is CommandButton5
-the input field below that button is TextBox1
-the last column with data in your sheet is column NN

try:

Private Sub CommandButton5_Click()
    Application.ScreenUpdating = False
    Dim lCol As Long, rng As Range, ws As Worksheet, v As Variant
    Set ws = Sheets("blank")
    ws.Columns.Hidden = False
    lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    For Each rng In ws.Range("A1:NN1")
        If rng Like "*" & TextBox1.Value & "*" Then
            ws.Columns(rng.Column).Hidden = False
        Else
            ws.Columns(rng.Column).Hidden = True
        End If
    Next rng
    Unload Me
    Application.ScreenUpdating = True
End Sub

Change the sheet name (in red) to suit you needs.