Filtering in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SteveW - 21 Feb 2006 14:40 GMT

I need help with the Autofilter
I have data in worksheet "SheetA" Cells A6 to D6 that has a list of names
In worksheet "SheetB" I have data from A2 to BB2 and in column AD have a
list of names that should contain multiple instances of the names in
SheetA!A6-D6
I would like to apply a filter from on SheetB!AD that corresponds to the
contents of SheetA!A6 the "do some work" then loop back to the contents in
SheetA!B6 and reapply a filter to SheetB!AD and "do some more work with
contents"

Make any sense ?

I already have some code:

Worksheets("SheetA").Activate
Range("A6").Activate
sPort = Range("A6").Text
iColumn = 1
iRow = 1
Do While sPort <> ""
'Do the filter bit here
'Loop to next value in A6 to D6
iColumn = iColumn + 1
sPort = Range("A6").Cells(1, iColumn).Text
Loop

As you may be able to see Im a bit of a newbie at VB so if someone can
provide any help it would be much appreciated

Thanks

Reply to this Message

Tom Ogilvy - 21 Feb 2006 15:48 GMT

Sub BCDE()
Dim rng As Range
Dim cell As Range
With Worksheets("SheetB")
Set rng = .Range(.Cells(1, "AD"), .Cells(Rows.Count, "AD").End(xlUp))
End With
For Each cell In Worksheets("SheetA").Range("A6:D6")
rng.AutoFilter 1, cell
' code that does something
MsgBox "cell: " & cell.Value
Next
rng.AutoFilter

End Sub

I assume there is a header in AD1. If not and you header is in AD2, then
change .Cells(1,"AD") to .Cells(2,"AD")

Signature

Regards,
Tom Ogilvy

> I need help with the Autofilter
> I have data in worksheet "SheetA" Cells A6 to D6 that has a list of names
[quoted text clipped - 26 lines]
>
> Thanks

Reply to this Message