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

 

[mumps]

Some of the cells in row 2 are not formatted as "General". For example, L2 is formatted as "Accounting". It looks like this causes a problem for the macro when trying to find the header "len". Highlight the entire row 2 and format it as "General" and try the macro again. Also, I suggest you delete all the unnecessary rows since they slow everything down including saving and opening the file. You also have some duplicate column headers in row 2 which will be a problem.

 

[sadams1]

Grrrrrr...sorry about not checking that. Otherwise, this is it! Thank you very much for going through all this.

 

[mumps]

You are very welcome.

 

[sadams1]

hello again! Sorry to bother but after using this for the past 2 years, I made some changes to the workbook & now it throws an error about "subscript out of range" with the "userform". Does this sound like something fixable?

 

[sadams1]

attached VBA screenshot

 

[mumps]

Please upload an updated file (not a picture) and explain in detail what the problem is.

 

[sadams1]

I kept messing around & found the reference I'd managed to screw up so sorry to bother! I use this on a regular basis even 2 years later!

 

[smozgur]

I changed the marked solution post with the actual answer in this thread.

@sadams1: In your future questions, please mark the post that answered your question as it will help future readers. If you post your own solution, it is perfectly fine to mark your own solution as the solution. No further action is required in this thread.

 

[sadams1]

I changed the marked solution post with the actual answer in this thread.

@sadams1: In your future questions, please mark the post that answered your question as it will help future readers. If you post your own solution, it is perfectly fine to mark your own solution as the solution. No further action is required in this thread.

my apologies....thought I'd marked it but it should be now.

 

[smozgur]

my apologies....thought I'd marked it but it should be now.

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.