Filter option

[joy_666]

Hi,

I am trying to delete records from the sheet using the filter option

I have used the below code

Selection.AutoFilter
Range("B1").Select
Selection.AutoFilter Field:=2, Criteria1:="False"
Rows("42:42").Select
Range("B2").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

The above macro deletes the data only if the data starts from row 2 when filtered, the macro deletes only the records starting from row 42....

I used the macro record option ......

How do I deleted all the records when I use a filter option.

In the above case I am filtering the value False and I want to delete all the records under that option......

Kindly suggest.......

 

[Colin Legg]

Hi,

Try something like this on a copy of your workbook. It will delete the entire row if FALSE is found in column C. Change Sheet1 for the name of the worksheet you are working on:

Sub foo()
    Dim rngToDelete As Range
 
    With Worksheets("Sheet1")
        .AutoFilterMode = False
 
        With .Range("B1:C1")
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:=False
        End With
 
        With .AutoFilter.Range
            On Error Resume Next
            Set rngToDelete = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
 
 
        If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
        .AutoFilterMode = False
 
    End With
End Sub

 

[joy_666]

Hi,

This works fine......

Thanks