some rows wanted

[JEPEDEWE]

I have this filtering problem

In the first row of my spreadsheet I have 3 values: B, LL and LK

I want to be able to filter the sheet on one of these values BUT, the title of the items shoul be seen as well:
example
in the attachement
when I choose to filter on LK I get to see all LK items together with their title (in this case 2.12) (and finally title 2 as well)

choose B I see all "B-items" with title 2.10 and 2.11 (and also title 2)

any idea?
Thanks
JP

[mancubus]

A75 = B / A80 = B / A84 = LKLL

font colors = white.
filter criteria = "contains"

[JEPEDEWE]

I made the extra changes you suggest but the rest isn't clear to me???

Would like a user-friendly "interface" to filter

Thanks

[mancubus]

sorry...
"contains" is not a filter criteria...


2003: Data | Filter | AutoFilter
click on the filter arrow for filter options.
select "custom"
select "contains" (left) - your criterion (right) (B or LL or LK)

2010: Data | Sort & Filter | Filter
click on the filter arrow for filter options.
select "text filters"
select "contains"
your criterion (right) (B or LL or LK)

we use contains because you have multiple values for heading 2.12 (LL, LK)

[mancubus]

you may also like easyfilter add-in

http://www.rondebruin.nl/easyfilter.htm

[JEPEDEWE]

hey,
thanks for your reply

The sheet is ment to be used by some collegues in our school, not really excel-users
This sheet all has to do with safety procedures for our (disabled) children
that is why I would like to have a more user-friendly interface then learning them to walk through te different menu-items
Installing extra add-in.... could be OK but I doubt it...

So maybe......

Thanks

[shrivallabha]

You can do this using VBA. People there need to Enable Macros and then following macro will work when users Double Click in Cell A1 provided they have a valid entry. To use this code, you need to right click on the Sheet Tab and then choose "View Code" option. Paste this code in the new window that pops up.
[vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rMatch As Range, rHide As Range
Dim lLastRow As Long
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
Cells.EntireRow.Hidden = False
If Target.Address = "$A$1" And Target.Value <> "" Then 'Refers to cell A1
For i = lLastRow To 2 Step -1
If Range("A" & i).Value = Target.Value Then
If rMatch Is Nothing Then
Set rMatch = Range("A" & i)
Set rMatch = Union(rMatch, Range("B" & i).End(xlUp).Offset(, -1))
Else
Set rMatch = Union(rMatch, Range("A" & i))
Set rMatch = Union(rMatch, Range("B" & i).End(xlUp).Offset(, -1))
End If
End If
Next i
For i = lLastRow To 2 Step -1
If Intersect(rMatch, Range("A" & i)) Is Nothing Then
If rHide Is Nothing Then
Set rHide = Range("A" & i)
Else
Set rHide = Union(rHide, Range("A" & i))
End If
End If
Next i
End If
If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True
End Sub
[/vba]

And in case they need to remove row hiding then use the sub below:
[vba]Public Sub ResetHidden()
Cells.EntireRow.Hidden = False
End Sub
[/vba]

[JEPEDEWE]

Hey thanks a lot...

I did what you suggested...
It works but, not fully correct
I entered B, doubleclicked... got about 5 rows with LK in column 1
Can you leave the blanc lines between the chapters?
Can you leave the first 8 rows?

Is it possible to have a dropdown box in cell A1, now I have the coise of B, LK or LL but maybe there will be more in the future

See you (hopefully)

(if you want I can send you the file, but don't want to bother you too much)
JP

[shrivallabha]

1. For Dropdown, look into Data Validation.

2. Rest of the requirements shall now be satisfied with:
[VBA]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rMatch As Range, rHide As Range
Dim lLastRow As Long
Cells.EntireRow.Hidden = False 'Sequence was incorrect here
lLastRow = Range("A" & Rows.Count).End(xlUp).Row 'Sequence was incorrect here
If Target.Address = "$A$1" And Target.Value <> "" Then 'Refers to cell A1
For i = lLastRow To 9 Step -1 'First 8 rows ignored
If Range("A" & i).Value = Target.Value Then
If rMatch Is Nothing Then
Set rMatch = Range("A" & i)
Set rMatch = Union(rMatch, Range("B" & i).End(xlUp).Offset(, -1))
Else
Set rMatch = Union(rMatch, Range("A" & i))
Set rMatch = Union(rMatch, Range("B" & i).End(xlUp).Offset(, -1))
End If
ElseIf Range("B" & i).Value = "" Then
Set rMatch = Union(rMatch, Range("A" & i))
End If
Next i
For i = lLastRow To 9 Step -1 'First 8 rows ignored
If Intersect(rMatch, Range("A" & i)) Is Nothing Then
If rHide Is Nothing Then
Set rHide = Range("A" & i)
Else
Set rHide = Union(rHide, Range("A" & i))
End If
End If
Next i
End If
If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True
End Sub
[/VBA]

3. I am attaching the sample workbook that I used for testing.

[JEPEDEWE]

Hallo,

I get an error 5 message at following line:

Set rMatch = Union(rMatch, Range("A" & i))

When I enter LK or LL or something completly different

When I enter B, the same problem as before

Sorry

JP

[shrivallabha]

Excel is acting weird when I am trying to test it with your workbook.

I have added error handling part and set enableevents to false if the code causes them.

[VBA]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rMatch As Range, rHide As Range
Dim lLastRow As Long

Cells.EntireRow.Hidden = False 'Sequence was incorrect here
lLastRow = Range("A" & Rows.Count).End(xlUp).Row 'Sequence was incorrect here

Application.EnableEvents = False

If Target.Address = "$A$1" And Target.Value <> "" Then 'Refers to cell A1
For i = lLastRow To 9 Step -1 'First 8 rows ignored
If InStr(Range("A" & i).Value, Target.Value) > 0 Then
If rMatch Is Nothing Then
Set rMatch = Range("A" & i)
Set rMatch = Union(rMatch, Range("B" & i).End(xlUp).Offset(, -1))
Else
Set rMatch = Union(rMatch, Range("A" & i))
Set rMatch = Union(rMatch, Range("B" & i).End(xlUp).Offset(, -1))
End If
ElseIf Range("B" & i).Value = "" Then
If rMatch Is Nothing Then
Set rMatch = Range("A" & i)
Else
rMatch = Union(rMatch, Range("A" & i))
End If
End If
Next i
If rMatch Is Nothing Then MsgBox "The specified keyword is not found!": GoTo ErrorHandle
For i = lLastRow To 9 Step -1 'First 8 rows ignored
If Intersect(rMatch, Range("A" & i)) Is Nothing Then
If rHide Is Nothing Then
Set rHide = Range("A" & i)
Else
Set rHide = Union(rHide, Range("A" & i))
End If
End If
Next i
End If
If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True
ErrorHandle:
Application.EnableEvents = True
Set rMatch = Nothing
Set rHide = Nothing
End Sub
[/VBA]

[JEPEDEWE]

I copied an pasted your code
did a B filter
All B values were deletd from column A
and the filtering wasn't correct either... no errormessages

sorry mate

JP

[shrivallabha]

This is not my day. And I realize, I am sc***ing yours as well, I am sorry. Try this routine and see if it works as per basic logic:
[vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rToCheck As Range, r As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Cells.EntireRow.Hidden = False
If Target.Value <> "" And Target.Address = "$A$1" Then
Set rToCheck = Range("A9:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each r In rToCheck
If r.Offset(, 1).Value = "" Then
'Do nothing
ElseIf InStr(r.Value, Target.Value) = 0 Then
r.EntireRow.Hidden = True
End If
Next r
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub[/vba]

[JEPEDEWE]

Hey, may thanks for your code

I think the filtering is correct now...
Column A is hidden now and I changed the cell to enter the filteringvalue to B1...

no problem so far, but, when you enter "LL" as filter the screen is very cluttery... a lot of space between the different titles...
I hope it is possible to do something about this??

Also, I don't arrive in entering a listbox to choose the filter-value from

Hope you are willing to help a bit more

Thanks you so much (attache the modified file)

JP, Belgium

[shrivallabha]

OK. Here's a version which I think is better on all counts. I have added data validation (B, LL, LK) to cell B1. I have added "ALL" to the list if in case a person wants to see ALL cases. I have changed event from 'double click' to 'change' so in effect you will see the sheet getting changed as soon as you change cell B1.
I have split the sub in two parts with a public variable rTarget to pass values from worksheet change.
[VBA]Public rTarget As Range
[/VBA]
Then worksheet change based event
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> "ALL" And Target.Address = "$B$1" Then
Set rTarget = Target
Call HideSpecificRows
Else
Cells.EntireRow.Hidden = False
End If
End Sub[/VBA]
Which calls this routine if the keyword is other than 'ALL'.
[VBA]Private Sub HideSpecificRows()
Dim rToCheck As Range, r As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Cells.EntireRow.Hidden = False
Set rToCheck = Range("A8:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each r In rToCheck
If InStr(r.Value, rTarget.Value) = 0 Then
r.EntireRow.Hidden = True
End If
Next r
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
[/VBA]

I have attached workbook.

[JEPEDEWE]

first impression.... perfect... so many thanks
JP

[JEPEDEWE]

I tested the worksheet... it works just fine now! thanks a lot...
Don't know if I dare to ask something extra....
Is it possible to have a "colour-filter" too...
Suppose I want to get all items with a red rectangle... (dangerous situations for our kids) can I filter them out as well
(either together with the filter you created or seperated)
hope it is possible, would be great... would make the info very easy to get...
Thanks for your efford, whish I could do something in return
JP

[JEPEDEWE]

maybe a bit of informaion about the sheet..

We use it to give our disabled kids a better treatment
When treating our kids, you have to look out for some processes
THe filter you already made, gives a list of processed importoand for 3 different groups of therapists
the colour rectangle highlights the importnace (sometimes danger) of the treatment
So getting a filter on both items could highlight all dangerous or safe treatments per therapist.... would be a great tool!
Thanks (also in name of our kids and therapists)
JP

[shrivallabha]

Sorry for late reply, it was my son's 2nd birthday so busy with him and family.

Its pretty much gettable idea. Could you tell me which cell / column shall I refer to. We can place this filter adjacent to the the first one.

[JEPEDEWE]

it all has to do with columns L M N

Happy birthday to you son... mine is 24 so... long time ago

Thanks
JP