.Find using Ranges to Hide/Unhide Columns

[J_Raab]

So I'm missing something simple but I just can't see it. I'm using .Find to search cells in a range("J6:BFA6") dim'd as "Name" for a user entered word from range("E5") dim'd as "NameS". If the value isn't found, the column becomes hidden, if it's found the column unhides. It hides the columns no problem, issue is it hides them all and won't keep the columns with the searched word unhidden. The cells range "Name" all contain equations, pulling values from cells in another worksheet, but would that matter? Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim NameS As Range
Set NameS = Range("E5")


Dim Name As Range
Set Name = Range("J6:BFA6")


Set NameF = Name.Find(What:=NameS, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)


If Target.Address(0, 0) <> "E5" Then
Exit Sub
ElseIf Target.Address(0, 0) = "" Then
Exit Sub
End If


For Each Cell In Name

If NameF Is Nothing Or Cell.Value = "" Then
Cell.EntireColumn.Hidden = True
Else
Cell.EntireColumn.Hidden = False
End If

Next Cell


End Sub

 

[svendiamond]

First, don't use "Name" as a variable. That's like using "String" as a variable. Second, you're only searching for the "NameS" one time. So you're not going to find all of them, just the first match. You really don't need to use Range.Find at all here, though.

Could you try something like this?

Sub hideColumns()

Dim myRange As Range, myCell As Range, searchTerm As String
Set myRange = Range("J6:BFA6")
    searchTerm = Range("E5")

For Each myCell In myRange
    If myCell <> searchTerm Then
        myCell.EntireColumn.Hidden = True
    Else
        myCell.EntireColumn.Hidden = False
    End If
Next myCell

End Sub

 

[J_Raab]

The issue still stands unfortunately, and this is my fault, I forgot to add that myRange is actually employees full names, so "John Smith", but the searchTerm is listed as "Last name only". So just doing a For...Next will hide all columns, since it'll never return an exact match. So the macro needs to scan the data in myRange for the value entered into searchTerm and unhide or hide the columns based on if the searched string is found anywhere in myRange (so searching for Smith would unhide columns John Smith, Jane Smith, Alex Smith, Smith Allen; but hide John Adams, Mark Stevens, etc).

I understand the range dim though. I was using "Name" strictly so i could keep the search criteria separate, this worksheet has multiple ranges that manipulate the page but those are based off of drop down menus, so I kept them specific so I could keep straight on what data I was actually referencing. Those all work without an issue and I have those tied to a form control button. My team wanted the last name to run off a worksheet change. The file has a ton of information to go through so having it run the macro for the drop downs and search the names at the same time would take forever, so it was decided they would run off two different triggers .

Honestly, I'm probably overthinking it and have been staring at a computer screen to long for whatever I'm doing wrong to register.

 

[svendiamond]

So just doing a For...Next will hide all columns, since it'll never return an exact match.

You still don't need to use Range.Find, just change the operator from "=" to "Like" so that anything "Like" "*Smith*" will count.

I understand the range dim though. I was using "Name" strictly so i could keep the search criteria separate

You shouldn't use "Name" as a variable because "Name" is an object/command that is used in VBA language. It can cause confusion, make your code messy, but more importantly, could potentially result in an error or non-functioning code.

Here is the same code, adjusted for these things and also triggered by the Worksheet_Change event now:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E5")) Is Nothing Then

    Dim myRange As Range, myCell As Range, searchTerm As String
    Set myRange = Range("J6:R6")
        searchTerm = Range("E5")
    For Each myCell In myRange
        If myCell Like "*" & searchTerm & "*" Then
            myCell.EntireColumn.Hidden = False
        Else
            myCell.EntireColumn.Hidden = True
        End If
    Next myCell

End If

End Sub

 

[J_Raab]

Wow, that's great. Thanks. I'm 99% self taught so I have the basics for vba, but the minutia sometimes escapes me. I'll have to look more into what you did there so I can apply the process to other macros i write in the future. Thanks, really appreciate it.

 

[svendiamond]

You're welcome. But you did most of the work. All I did was drop the "Find" from your code, adjust the For/Next loop to search for anything "Like" the target value, and re-arrange the way you look for E5 to be changed.


If you only want events to activate for certain cells, always use:

If Not Intersect(Target, yourRangeHere) Is Nothing Then
'code to activate
End If

To find things that contain "Smith" use

If yourString Like "*Smith*" Then

Thanks for using the forum. I like solving these issues and helping people out.