Deleting rows using filters

[bruty]

I have the need to delete rows in a spreadsheet based on criteria picked out using a filter.

I am using VBA to try and automate this, but as the row numbers change and sometimes the selections are blank I am having trouble only deleting the selections pulled out from the filters.

Is there any easy trick I am missing?

Anything that can be suggested would be greatly appreciated.

 

[wigi]

For instance, here's an example of an Autofilter to filter for 0's in the first column column:

    With Sheets("NameOfTheSheet").UsedRange
        .AutoFilter 1, 0
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With

You should provide the name of the sheet where I indicated: Sheets("NameOfTheSheet")
Or, you could use ActiveSheet instead.

Wigi

 

[Akinrotimi]

Hi Bruty,

Can you explain what the criteria is so that I help out? You should be able to use if function to resolve this.

Kindly expatiate with a possible screen shot.

Regards

Rotimi

 

[bruty]

Hi Bruty,

Can you explain what the criteria is so that I help out? You should be able to use if function to resolve this.

Kindly expatiate with a possible screen shot.

Regards

Rotimi

There are several criteria, but one of them is I need to select any rows which contain CSS, ILMS and ISLM in Column B and delete them from the data. If I was guaranteed to have this data I can work it out, but sometimes the filter will bring our blank and this is what causes my problem.

Can't do a screenprint as on my work computer and they're very limited.

 

[bruty]

This is the general code I'm using:

    Selection.AutoFilter Field:=2, Criteria1:="=*CSS*", Operator:=xlAnd
    Rows("78:78").Select    'This bit causes a problem as it won't always be this row to delete
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=2, Criteria1:="=*ILSM*", Operator:=xlOr, _
        Criteria2:="=*ISLM*"
    Rows("78:78").Select    'This bit causes a problem as it won't always be this row to delete
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp

This works with the current data, but when it is next updated the row of the first selection will change, messing the code up, so I just need to locate the first selection in the filters - hope this makes sense...

 

[Akinrotimi]

The code below would solve the problem:

================================================
Application.ScreenUpdating = False
Range("b10011").Select
ActiveCell.FormulaR1C1 = "css"
Range("b10012").Select
ActiveCell.FormulaR1C1 = "ISLM"
Range("b10013").Select
ActiveCell.FormulaR1C1 = "ILMS"
Columns("B:B").Select
Selection.Replace What:="CSS", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ISLM", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ILMS", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("a1").Select
Application.ScreenUpdating = True

============================================

Regards

Rotimi

 

[bruty]

Cheers will give it a go