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
"tess457 " wrote in message
...
I have been trying to filter out a large database, here are the two
approaches I have tried:
I tried filtering the data using a for loop, but with this code, it
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 cannot
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 filter
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.....I
would appreciate any help or suggestions that you may have!!! I thank
you in advance for your consideration.
Dustin
Attachment filename: test.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=696984
---
Message posted from http://www.ExcelForum.com/