View original (French)

Removing rows resulting from a filter

Solved
Druddy Posted messages 34 Status Membre -  
Druddy Posted messages 34 Status Membre -
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 réponses

Réponse 1 / 4
f894009 Posted messages 17417 Registration date   Status Membre Last intervention   1 717
 
Hello,

Among other examples
at the bottom of the page
https://www.developpez.net/forums/d1713575/logiciels/microsoft-office/excel/contribuez/filtre-suppression-lignes-vba/
0
Réponse 2 / 4
Druddy Posted messages 34 Status Membre
 
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
Réponse 3 / 4
Le Pingou Posted messages 12375 Registration date   Status Contributeur Last intervention   1 476
 
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
Réponse 4 / 4
Druddy Posted messages 34 Status Membre
 
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