Advanced Filter Macro

**jason_1** · 11-14-2009, 09:39 AM

Hi

I have a list of records with one colomn, column F listing the type of record, each assigned a letter.

I need to filter all records with B, C, J, M, O and move all those records to a new sheet (Sheet2) and delete from original sheet ("Data")

Many thanks

**Richard Buttrey** · 11-14-2009, 10:13 AM

Hi

Create a list which contains the column header of your B,C,J etc records, and list the B,C;J underneath and name the whole range 'crit'

Name the whole of your data 'data' and include the filed headings.

Now run the following macro

Sub FilterCopyDelete()
    Range("Data").AdvancedFilter Action:=xlFilterInPlace, criteriarange:=Range("crit")
    Range("Data").Offset(1, 0).Resize(Range("data").Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy
    Sheets.Add
    Range("A1").PasteSpecial (xlPasteAll)
    Range("Data").Offset(1, 0).Resize(Range("data").Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Range("Data").AutoFilter

End Sub

HTH