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

 

[Harley78]

Listbox does not have any selections, unless I manually create a list and link it to the FillListRange.

Then when i do a selection I get

Runtime error 13
Type mismatch

 

[MickG]

Hi If your not in Design Mode you won't be able to Set the "ListFillRange" Property.(See previous post)
Mick

 

[Harley78]

I decided to start fresh and check step by step what the problem could be,

I created a listbox1 as stated, right clicked and changed to multiselect. Tried both, Macro create "Hide" and also a right clicked the sheet tab and created the Hide Macro. nothing filled in the Listbox1. I stopped there since nothing is in the box, the second code won't work. I scrolled through the macro and it shows the following:

Sub Hide()
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("Ak2"), Range("Ak" & Rows.Count).End(xlUp))
‘(Range(“Ak2”) =503 which is correct! Rows.Count = 65536 which is correct! Xlup=-1462

With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
vbTextCompare = 1
For Each Dn In Rng
DN = nothing

If Not .Exists(Dn.Value) Then
DN value = 503 and if you keep pressing F8 it goes to the next which is correct! .Add Dn.Value, ""
End If
Next
ActiveSheet.ListBox1.List = .keys
End With

End Sub

So i am not quite sure what the problem might be

 

[Harley78]

Second Part,

If I go to ListBox1 Properties and Enter AK2:AK10 FillListRange, it will show the selections with duplicates. Now when I do my selections it will hide only the first selection and an error

Runtime error 13
Type mismatch

I can select individually and it will work somewhat and then give me the same error

 

[Harley78]

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

For Del = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Del) Then
ListBox1.List(Del)=false & Del=2[/
For Each Dn In Rng
DN= nothing
If Dn = ListBox1.List(Del) Then
ListBox1.List(Del)=0
Dn.EntireRow.Hidden = True
Dn.EntireRow.Hidden = <Object variable or With block variable not set

End If
Next Dn
End If
Next Del Del=5
End Sub

So far these are my issues

I do appreciate you input and help so far....

 

[MickG]

Hi, Try this Example:-

http://www.4shared.com/file/92849352/2ccec860/Hide_Hide.html
Regards Mick

 

[Harley78]

that worked exactly how I wanted it to.

However, when I copied my worksheet into this form, it would hide the first two and then give me an error ( Run Time Error 13 Type Mismatch.

I can now assume it might be the formating of the cells it is looking at. Boy, I am stumped!

Many thanks Mick

 

[MickG]

Hi, Can you show an example of the data you have in column "AK" and any Underlying formula or Formatting.
Regards Mick

 

[Harley78]

Not quite sure how to show all the fomats, but here is the sample of the column

Excel Workbook
	AK
1	CATEGORY
2	ENDURO/JV
3	ENDURO/JV
4	ENDURO/JV
5	XXX
6	XXX
7	ENDURO/JV
8	PMII/JV/503
9	X-502
10	ENDURO
11	ENDURO
12	X-502
PARTS LIST

Now I notice that the XXX should be DW, not quite sure why it is coming up, However, even if the others are selected, error is still there.

Just another note:
I have also, tried, copy and paste values and that doesn't seem to help either.

 

[MickG]

Hi , I've tried calling the column "AK" Data from another workbook Based on on you lookup formula, but I still can't find the Error.
Can only suggests you try this "AK" column with Just Basic Text as per you list , see if error remains.
Mick