Filtering Data

[tess457]

I have been trying to filter out a large database, here are the tw
approaches I have tried:
I tried filtering the data using a for loop, but with this code, i
only filters one variable at a time, it will give me all the "HR"s
then all the "CC"s, etc. I need to get the HR,CC, TA,etc all together
and not one at a time.

Dim rnData As Range
Dim vaConditions As Variant
Dim i As Long

vaConditions = VBA.Array("HR", "CC", "TA")

Application.ScreenUpdating = False

With ActiveSheet
For i = 0 To 2
Set rnData = .UsedRange
With rnData
.AutoFilter Field:=3, Criteria1:=vaConditions(i)
'.Offset(1, 0).Resize(.Rows.count - 1, .Columns.count
_
'.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
.AutoFilterMode = False
.UsedRange
Next i
End With

Application.ScreenUpdating = True

I have also tried using the advanced autofilter feature, but I canno
get it to work properly, I have tried it with elementary data values
and it works, but when I try to apply it to my data, it doesnt filte
anything out.

Columns("C:C").AdvancedFilter Action:=xlFilterInPlace
CriteriaRange:=Range("F2:F6"), Unique:=False

I have included a sample of the database that I am working with.....
would appreciate any help or suggestions that you may have!!! I than
you in advance for your consideration.

Dusti

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=69698

 

[Art Farrell]

Hi,

I didn't download your file and I am assuming your criteria for deleting are
all in row C. You can use the following macro which uses the AdvancedFilter
which copies the output to a new sheet.

Sub Dustin()
Dim lastrow As Long

Application.ScreenUpdating = False
Rows("1:2").Insert Shift:=xlDown
Range("H2").Formula = "=AND(C4<>""HH"",C4<>""CC"",C4<>""TA"")"
lastrow = Cells.SpecialCells(xlLastCell).Row
Sheets.Add after:=ActiveSheet
ActiveSheet.Previous.Rows("3:" & lastrow).AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=ActiveSheet.Previous _
.Range("H1:H2"), CopyToRange:=Range("A1"), Unique:=False
ActiveSheet.Previous.Rows("1:2").EntireRow.Delete
End Sub

CHORDially,
Art Farrell