Delete Rows After a filter

**koda86** · 11-24-2008, 01:59 AM

Hello,

I used to work a lot on VBA but now I forgot almost everything and I would need your help to restart again my work on VBA. I have a worksheet with data from Column A to AA and I have a filter on Row 1. I would like to select from this filter a certain value for column D and then delete the rows I dont need. So my code starts like this:

 Sheets("Export").Select
 Selection.AutoFilter Field:=4, Criteria1:="<>MY", Operator:=xlAnd

But after that I would like to delete the rows that are matching the criteria <> MY. Do I need to select manually the rows and then after make Selection.delete or does it exist a code that can directly delete the rows that I have selected (<> MY) ?

Thank you for your help !!!
please Tell me if my description is not clear... so much time I have past since my last visit on this website !

**yeaster** · 11-24-2008, 03:06 AM

Hi,

I'm not sure if this will help but this was the code I used delete the rows away after using auto filter. You could use it to adapt it to your own code.

    With ActiveSheet
             If .AutoFilterMode = False Then Selection.AutoFilter
                    .Range("C4").AutoFilter Field:=1, Criteria1:="#N/A"
                    .Range("C4").CurrentRegion.Offset(1, 0).SpecialCells _
            (xlCellTypeVisible).EntireRow.Delete
            .AutoFilterMode = False
    End With

Do let me know if this works.

**koda86** · 11-24-2008, 04:01 AM

Thanks yeaster for your response !! But I have some troubles in converting your code to my sheet: it must something like:

Selection.AutoFilter Field:=4, Criteria1:="<>MY", Operator:=xlAnd
.................(xlCellTypeVisible).EntireRow.Delete

But between the two lines, I cannot find what to put before (xlCellTypeVisible).EntireRow.Delete.

What did you try to do with your line (.Range("C4").CurrentRegion.Offset(1, 0).SpecialCells _) ? I am pretty sure I need to adapt this last one but I really dont know what this line does.

Thanks

**yeaster** · 11-24-2008, 05:52 AM

I used this part

.Range("C4").CurrentRegion.Offset(1, 0).SpecialCells

to avoid deleting my header row, hence there's the

offset(1,0)

[which is the first row].

Maybe if you still can't get it working right, you could post a sample spreadsheet for me and I can show you how to use it.