Solved: filter bloks

[white_flag]

Good morning
It is possible to make an filter that will filter blocks?
Blocks are:5rows then 5 rows etc. so I have:
name______________date______________another field
row1 -name________row1-date_________row1-anotherfield
row2
row3
row4
row5
row6 -name________row6-date_________row6-anotherfield
row7
row8
row9
row10
row11 -name________row11-date_________row11-anotherfield
etc

If I chose date, filter will show blocks that will contain filtering criteria

[Bob Phillips]

Oh dear! I had an idea how to achieve this and was going to spend some time coding it up, then I found it was cross-posted at MrExcel, http://www.mrexcel.com/forum/showthread.php?649689-filter-quot-blocks-quot

[white_flag]

it was like this ...

first, search on internet (because, I have no idea how to do it).
then putted on vbaexpress
waiting ...
waiting ...
then, putted on mr.excel (with cross post remark)

so this is bad?
for me I just need a direction on this case

[Bob Phillips]

To me, yes it is bad. You say waiting ..., waiting .... I had replied with one and a half hours, that includes thinking about how to achieve it, doing some preliminary tests, and then finding it elsewhere. For something that you can't do yourself and is not trivial, the lack of patience is staggering.

And once I know it is elsewhere, I am not going to potentially waste my time any further. There might be someone there also working on it, putting in a lot of effort, one of us would be wasting time.

[white_flag]

ok, I understand. (mea culpa), today, no more nice weather.
anyway ...an idea to start? time is pressing me.

[Bob Phillips]

Okay, I will outline my idea, and leave it at that.

You would, manually, use the Data>Group facility to setup the row blocks.
Then you would filter on the target column.
Then run a macro that would:-
- evaluate the filter conditions
- loop through each group to get the start and end
- check each line of the group and see if it matches any of the conditions
- if match, unhide all rows in that group

[white_flag]

sounds very good.. thx Bob.
have a nice day!

[white_flag]

Option Explicit
Sub create_blocks()
'from 5 to 5 rows
Dim lngRow As Long
For lngRow = 5 To ActiveSheet.UsedRange.Rows.Count Step 5
ActiveSheet.Rows(lngRow & ":" & lngRow + 3).Group
Next
With ActiveSheet.outline
.SummaryRow = xlAbove
.SummaryColumn = xlRight
.AutomaticStyles = True
End With
End Sub

Sub AutoFilter_blocks()

Dim oWS As Worksheet

On Error GoTo Err_Filter

Dim arCriteria(0 To 4) As String


Set oWS = ActiveSheet


arCriteria(0) = oWS.range("F3").Value
arCriteria(1) = oWS.range("G3").Value
arCriteria(2) = oWS.range("H3").Value
arCriteria(3) = oWS.range("I3").Value
arCriteria(4) = oWS.range("J3").Value

oWS.range("A3:" & "X" & ActiveSheet.UsedRange.Rows.Count).Cells.AutoFilter Field:=2, Criteria1:=arCriteria, Operator:=xlAnd


For i = 1 To ActiveSheet.UsedRange.Columns.Count
With range("F3")
.AutoFilter Field:=i, VisibleDropDown:=False
End With
Next
With range("F3")
.AutoFilter Field:=6, VisibleDropDown:=True
.AutoFilter Field:=7, VisibleDropDown:=True
.AutoFilter Field:=8, VisibleDropDown:=True
.AutoFilter Field:=9, VisibleDropDown:=True
.AutoFilter Field:=10, VisibleDropDown:=True
End With

ActiveSheet.Columns("A").ColumnWidth = 22
ActiveSheet.Columns("F:J").ColumnWidth = 15
ActiveSheet.Columns("K:AA").AutoFit

Finally:

If Not oWS Is Nothing Then Set oWS = Nothing

Err_Filter:
If Err <> 0 Then
MsgBox Err.Description


Err.Clear
GoTo Finally
End If
End Sub


one more time. thx Bob for direction.