Excel filtering

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

I have a worksheet with a structure of different levels,
e.g.

Lev1, Lev2, Lev3, Lev4 as the headings

I want to create an index of occurences of a particular
name on another worksheet in the same workbook. e.g.

Lev1 has the word DAVID in the column and on the same row
there are other names in different Lev headings. I want
the index to reproduce all lines in this structure where
DAVID is contained. I think its sort of a filter function.

I agree this is hard to explain. Let me know if you have
any ideas, or whether i should explain it better.

Thanks in advance

Jon
 
This doesnt copy the results to a new sheet.

I know this function and it doesnt do the job i'm
explained.

Thanks
John
 
Copy the visible cells and Edit>Paste Special>Values to a new sheet. If you
want to do it repeatedly, record a new macro while doing it.
 
See if this helps. Modify to suit.

Sub CopyFiltered()
x = Sheets("sheet4").Cells(Rows.Count, "a").End(xlUp).Row + 1
With Range("A1:C21")
.AutoFilter field:=1, Criteria1:="10"
.SpecialCells(xlVisible).Copy _
Sheets("sheet4").Cells(x, "a")
.AutoFilter field:=1
End With
End Sub
 
Ok. Thanks for the help. But this doesnt do what i need.

If i have a sheet with lots of data in i want to filter it
to show me all the rows with the word DAVID in. Not one
particular column as this is what your suggestion of
filtering does. Then i want to create a macro of somekind
to display these rows in another sheet. The autofilter
doesn't work for a range of columns only individually.

Any suggestions.

Thanks for the help,

Jon
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook (watch out
for word wrap):

Sub testIt1()
Dim rng As Range, arr As Variant, i As Long
Set rng = Sheets("Sheet2").Range("A1:F7")
arr = ArrayMatch("David", rng)
For i = 1 To UBound(arr)
Sheets("Sheet5").Range("A" & 1 + i - 1).Resize(,
rng.Columns.Count).Value = ArrayRowFilter1(rng, arr(i, 2), "David")
Next
End Sub

Alan Beban
 
Back
Top