PDA

View Full Version : Best approach to filter on a value, but showing all lines concerned by same object?



ValerieT
09-05-2013, 02:01 AM
Hello. I have no idea how to start thinking about this:

In this example, a standard filter on value= 2 would show ligne 3 and 6


1

Per
Obj
Value


2
V1
*
A
1


3
V1
*
B
2


4
V1
1
A
1


5
V2
*
A
1


5
V3
*
A
4


6
V3
1

2



I need to show, for the same filter value=2, all the lines concerning V1 and V3: because V1 has a value 2, I must show all of it.. meaning the end result must be lines 2,3,4,5,6 (the file is huge both in lines and columns)

Thanks to share if you have any ideas...

snb
09-05-2013, 06:34 AM
Maybe:


Sub M_snb()
With Cells(1).CurrentRegion
.AutoFilter 4, 2
.Columns(1).Offset(1).Copy Cells(1, 10)
.AutoFilter
.AutoFilter 1, Columns(10).SpecialCells(2)
End With
End Sub

ValerieT
09-05-2013, 07:55 AM
Hello again snb

Merci :-)

It seems to work for one line... and it made me think: I'll let the user do its own classical filter, then only he activates the macro to enlarge to all related lines.

So, now I've got another question: if I need to loop on all visible row only, how can I get the number to process? (meaning counting the visible lines after filtering)
I struggle with something like that:

Endlign = Cells.SpecialCells(2).SpecialCells(12).SpecialCells(11).Row - 1

but no matter the order or syntaxe variant, I always endup with 0 or the worksheet total lines