View original (French)

Removing rows resulting from a filter

Solved
Druddy Posted messages 34 Status Member -  
Druddy Posted messages 34 Status Member -
Hello Everyone,

Following some filters, I would like to delete the resulting rows from my filters (except for the 1st one: Title of my columns).
The 1st cell varies depending on the result (e.g.: Rows("3:3").Select or Rows("7:7").Select).
Manually, I get: 

'********************* ' DelNonINC Macro '********************* Sub DelNonINC() ActiveSheet.ListObjects("TabDatas").Range.AutoFilter Field:=8, Criteria1:= _ "<>INC*", Operator:=xlAnd Rows("3:3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Delete Shift:=xlUp ActiveSheet.ListObjects("TabDatas").Range.AutoFilter Field:=8 End Sub '********************* ' DelMax0 Macro '********************* Sub DelMax0() ActiveSheet.ListObjects("TabDatas").Range.AutoFilter Field:=11, Criteria1:= _ "0" Rows("7:7").Select Range(Selection, Selection.End(xlDown)).Select Selection.Delete Shift:=xlUp ActiveSheet.ListObjects("TabDatas").Range.AutoFilter Field:=11 End Sub


Thank you in advance,

4 answers

  2.
    Druddy Posted messages 34 Status Member
     
    Hello,

    Thank you for the reference, but I must admit that my level does not allow me to understand the subtlety in this exercise from the provided link.
    I did not understand the definition of the range.
    If you could help me understand and adapt the macro? 


    Sub DelNonINC()
     derlig = Cells(Rows.Count, 1).End(xlUp)
     Set plage = Range("A3", Cells(derlig, 1))
     
     With plage
     .AutoFilter Field:=8, Criteria1:="<>INC*", Operator:=xlAnd
     Rows(plage.Row + 1 & ":" & plage.Row + plage.Rows.Count - 1).Delete
     .AutoFilter Field:=8
     .AutoFilter
     End With

    End Sub


    Thank you in advance,
    0
  3. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 478
     
    Hello,
    Just a quick note, the macro is likely returning an error on the line 
    Set Plage = ...
    , because the line: 
     derlig = Cells(Rows.Count, 1).End(xlUp)
    is returning the value of the last row instead of the row number.
    Please modify the line 
     derlig = Cells(Rows.Count, 1).End(xlUp).Row


    --
    Best regards.
    The Penguin
    0
  4. Druddy Posted messages 34 Status Member
     
    Thank you Le Pingou.

    Resolved with this code for those who want it: 

     '********************* ' DelNonINC Macro '********************* Sub DelNonINC() ActiveSheet.ListObjects("TabDatas").Range.AutoFilter Field:=8, Criteria1:= _ "<>INC*", Operator:=xlAnd Rows("3:3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Delete Shift:=xlUp ActiveSheet.ListObjects("TabDatas").Range.AutoFilter Field:=8 End Sub '********************* ' DelMax0 Macro '********************* Sub DelMax0() ActiveSheet.ListObjects("TabDatas").Range.AutoFilter Field:=11, Criteria1:= _ "0" Rows("7:7").Select Range("F7").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Delete Shift:=xlUp ActiveSheet.ListObjects("TabDatas").Range.AutoFilter Field:=11 End Sub
    0