Hide Suggestion

[Harley78]

I have a worksheet that contains over 15k rows, each row, in column AK has an input of X-123, x123, 123, ENVEL, ROL, WPL-503, etc. there are a total of 20 different inputs. These inputs are found on another file and inputted from with a Vlookup formula.

I tried advanced filter, but it is limited and thought, if I have a command button that opens a userform then I can select which ones I want to be hidden.

I don't know exactly if I should use a checkbox, listbox, combo box ect. or if you can suggestion an alternative.

Thanks

Bill

 

[MickG]

Hi, Try This:-
Place a Listbox on your sheet, set its Property to "Multi Select"
First Code Fills the listBox with Uniques from Column "AK".
Second Code "ListBox_Lost Focus" will Hide Rows In column "AK" if there value equals the selected Values From List Box.

Sub Hide()
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("Ak1"), Range("Ak" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For Each Dn In Rng
             If Not .Exists(Dn.Value) Then
               .Add Dn.Value, ""
             End If
    Next
ListBox1.List = .keys
End With
End Sub

Private Sub ListBox1_LostFocus()
Dim Rng As Range, Dn As Range, Del As Integer
Set Rng = Range(Range("Ak1"), Range("AK" & Rows.Count).End(xlUp))

For Del = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Del) Then
        For Each Dn In Rng
            If Dn = ListBox1.List(Del) Then
                Dn.EntireRow.Hidden = True
            End If
        Next Dn
End If
Next Del
End Sub

Regards Mick

 

[Harley78]

Hey Mick,

Thanks for the input and it looks like thats what I want. But I am stumped. I have added a Listbox directly on the sheet. Created the macro Hide and doesn't show in the listbox on the sheet. What might I be doing wrong?

So sorry about this...

 

[MickG]

Hi The "list box" should be from the "View" , Toolbars", "Control Toolbox", Menu. (My fault for not saying)
Make sure the the List Box in the code has the same Name.
Have a go with this see if it sorts the problem.
Regards Mick

 

[Harley78]

Okay, so you now know I am not up to speed on listboxes. Just so I know I following you. Create a macro called "Hide" and place the 1st macro code to that. Then create a listbox, and place the second code inside. And thats it!

when I do this, nothing shows in the listbox..

So sorry, i must be doing something wrong

 

[MickG]

Hi,Are you running a "Mac", If so I don't think you can use the "Scripting Dictionary".
Otherwise have you tried Stepping through the code To see if the values are being "Added" to the Dictionary.
Open VB Window with Code, Click In Code Window, Click (F8) (Line turns Yellow) to step throught the code, Hold curser over "Dn.Value", see if its, First Correct Value and Second, Being Added.
Regards Mick

 

[Harley78]

I am using microsoft xp and excel 2003. I go to tools, macro, select Hide macro and select edit and hit F8. When I hit F8, Compile error: variable not defined... and ListBox1 highlights in blue. My list box is labeled as ListBox1

 

[MickG]

Hi, Change the last Line to below. It's because your code is in a Module and the code doesn't know which sheet to refer to.

ActiveSheet.ListBox1.List = .keys

Hopefully that will fix it.
Mick

 

[Harley78]

That didn't help either. Should there be a ListFillRange? Or could it be that AK are formula's and not actual text?

 

[MickG]

Hi, Do you still Have an rerror, If so What !!
It's not important whether the values are Text or Values as a result of Formula, it should still work.
You could try First Replacing The last Line with the below.

ActiveSheet.Range("A1").Resize(.Count).Value = Application.Transpose(.keys)

This will fill "A1" with all the uniques from column "AK".
Then If you Right click the ListBox in "Design Mode" (Go to Control ToolBox" Menu Click "Green Triangle", The curser should show Crossed Arrows When you hold it over "ListBox".) then
select Properties from the sub menu, then find "ListfillRange" set The ListFillRange to something like: "A1:A100".
Try again
Have you tried stepping throught the code again ??
Mick