search text hide column

[eliozo]

hello!
Need your help in this:
I need to click a button, and once clicked tell me to search a name in row 2.

After clicking yes, the columns that doesn't contain this text will be hidden. and the columns that will contain it (or a part of it) will be shown.

Thank you

 

[davesexcel]

Check it out,

Sub Button1_Click()
    Dim LstCol As Long, sh As Worksheet, c As Range, s As String, Rng As Range
    
    Set sh = ActiveSheet
    
    With sh
    
        .Cells.EntireColumn.Hidden = False
        LstCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Set Rng = .Range(.Cells(2, 1), .Cells(2, LstCol))
        
        s = InputBox("What to find?")
        
        Set c = Rng.Find(what:=s, lookat:=xlPart)

        If Not c Is Nothing Then
            Rng.EntireColumn.Hidden = True
            .Cells(2, c.Column).EntireColumn.Hidden = False
        Else: MsgBox "Not Found"
            Exit Sub
        End If
        
    End With

End Sub

 

[Peter_SSs]

columns that will contain it (or a part of it) will be shown.

Can you clarify, including examples, of just what you mean by the blue part?

 

[eliozo]

thank you davesexcel but it didnt work dunno why!
Peter thank you for your reply, Find below an example:

I have a lot of names of clients starting with cell D2, i.e: Brian Leon, Tony David, Georges Peter.......
I want a search input box, if I put the name of Brian for example, I want all columns where the name Brian appears to be shown (including Brian Leon) and hide all other columns starting from Column D. Or for example, if I put in the search box "avid", i want the column of all names where there is "avid" to be shown, for example "Tony David" to be shown and hide all others.
Thank you so much guys

 

[Peter_SSs]

Try

Sub Show_Hide_Columns()
  Dim srchText As String
  Dim lc As Long, c As Long
  
  srchText = InputBox("Enter search text (not case-sensitive)")
  lc = Rows(2).Find(What:="*", LookIn:=xlFormulas, SearchDirection:=xlPrevious).Column
  If lc > 3 Then
    Columns(4).Resize(, lc - 3).Hidden = False
    If Len(srchText) > 0 Then
      For c = 2 To lc
        Columns(c).Hidden = InStr(1, Cells(2, c).Value, srchText, vbTextCompare) = 0
      Next c
    End If
  End If
End Sub

 

[eliozo]

Peter thank you. but it gave me an error on this line
lc = Rows(2).Find(What:="*", LookIn:=xlFormulas, SearchDirection:=xlPrevious).Column

 

[Peter_SSs]

Peter thank you. but it gave me an error on this line
lc = Rows(2).Find(What:="*", LookIn:=xlFormulas, SearchDirection:=xlPrevious).Column

What is the full error message?

 

[eliozo]

Object variable or With block variable not set

 

[Peter_SSs]

Object variable or With block variable not set

That would indicate to me that there is nothing in row 2 of the active sheet. You did say the names were in row 2.

 

[eliozo]

The names are actually in Cells 2&3 merged. When I click on a cell it tells me D2, E2, F2.. so as i was saying, they are in rows 2&3 merged. Should the code be changed in that case? thank you