Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Data
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Data
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filtering 3 data row | Excel Discussion (Misc queries) | |||
Filtering of data - autmatically based on data on other sheet | Excel Discussion (Misc queries) | |||
Need Help filtering data | Excel Worksheet Functions | |||
Data Filtering | Excel Discussion (Misc queries) | |||
Help with Filtering data and matching two data sets? | Excel Programming |