How do I Delete Rows on Range Criteria for Column A Cells Not Equalto specific values ?
 

Sub DelRowsOnCond()
Dim LRow As Long
'
'How do I create a Range of rows to delete for Criteria1 Not Equal to "F1PPM60601" OR F1PPM60549" Etc...
'
Range("a2").AutoFilter Field:=1, Criteria1:="<""F1PPM60601"",""F1PPM60549"",""F1PP M60623"""
'
'Exclude 1st row (Headers)
With Sheets("Invoices")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
'
'What is the right syntax to Delete Rows of the above defined Range
.Range("A2:A" & LRow).Rows.Delete
End With
ActiveSheet.ShowAllData
End Sub

Help Appreciated,
J.P.

How do I Delete Rows on Range Criteria for Column A Cells NotEqual to specific values ?
 

bonjour J.P.

..Range("A2:A" & LRow).SpecialCells(xlCellTypeVisible).EntireRow.De lete

isabelle

Le 2015-04-25 12:07, JeanPierre Charron a écrit :
Sub DelRowsOnCond()
Dim LRow As Long
'
'How do I create a Range of rows to delete for Criteria1 Not Equal to "F1PPM60601" OR F1PPM60549" Etc...
'
Range("a2").AutoFilter Field:=1, Criteria1:="<""F1PPM60601"",""F1PPM60549"",""F1PP M60623"""
'
'Exclude 1st row (Headers)
With Sheets("Invoices")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
'
'What is the right syntax to Delete Rows of the above defined Range
.Range("A2:A" & LRow).Rows.Delete
End With
ActiveSheet.ShowAllData
End Sub

Help Appreciated,
J.P.

How do I Delete Rows on Range Criteria for Column A Cells NotEqual to specific values ?
 

Merci depuis la Nouvelle Orleans, pour la reponse Isabelle,
mais j'ai des doutes sur la syntaxe :
'How do I create a Range of rows to delete for Criteria1 Not Equal to "F1PPM60601" OR F1PPM60549" Etc...
'
Range("a2").AutoFilter Field:=1, Criteria1:="<""F1PPM60601"",""F1PPM60549"",""F1PP M60623"""
..
I have serious doubts on my Range Criteria Syntax
Have a good day,
J.P.

How do I Delete Rows on Range Criteria for Column A Cells Not Equal to specific values ?
 

Hi J.P.,

Am Sat, 25 Apr 2015 09:07:13 -0700 (PDT) schrieb JeanPierre Charron:

'How do I create a Range of rows to delete for Criteria1 Not Equal to "F1PPM60601" OR F1PPM60549" Etc...

try:

Sub DeleteRows()
Dim LRow As Long, i As Long
Dim varData As Variant

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Sheets("Invoices")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:A" & LRow)

For i = UBound(varData) To LBound(varData) Step -1
If varData(i, 1) < "F1PCM60125" And _
varData(i, 1) < "F1PCM60104" Then
.Rows(i + 1).Delete
End If
Next
End With

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How do I Delete Rows on Range Criteria for Column A Cells NotEqual to specific values ?
 

bonjour J.P. de la Nouvelle Orleans,

microsoft a oublié de traiter cette partie (<) pour plus de 2 criteres
concernant le filtre automatique :(
Criteria1:="<1", Operator:=xlAnd, Criteria2:="<2"
mais il serait possible de le faire avec un filtre élaboré et une plage de critère.
un tuto sur le sujet ici:
http://philippetulliez.developpez.co...dvancedfilter/

isabelle de Sherbrooke, PQ

Le 2015-04-25 14:10, JeanPierre Charron a écrit :
Merci depuis la Nouvelle Orleans, pour la reponse Isabelle,
mais j'ai des doutes sur la syntaxe :
'How do I create a Range of rows to delete for Criteria1 Not Equal to "F1PPM60601" OR F1PPM60549" Etc...
'
Range("a2").AutoFilter Field:=1, Criteria1:="<""F1PPM60601"",""F1PPM60549"",""F1PP M60623"""
.
I have serious doubts on my Range Criteria Syntax
Have a good day,
J.P.

How do I Delete Rows on Range Criteria for Column A Cells NotEqual to specific values ?
 

another solution is to add a new column in table
with a formula :
=IF(OR(A7="F1PPM60601",A7="F1PPM60549",A7="F1PPM60 623"),1,0)
and apply the filter on this column

isabelle


Le 2015-04-25 16:24, isabelle a écrit :
bonjour J.P. de la Nouvelle Orleans,

microsoft a oublié de traiter cette partie (<) pour plus de 2 criteres
concernant le filtre automatique :(
Criteria1:="<1", Operator:=xlAnd, Criteria2:="<2"
mais il serait possible de le faire avec un filtre élaboré et une plage de critère.
un tuto sur le sujet ici:
http://philippetulliez.developpez.co...dvancedfilter/

isabelle de Sherbrooke, PQ

Le 2015-04-25 14:10, JeanPierre Charron a écrit :
Merci depuis la Nouvelle Orleans, pour la reponse Isabelle,
mais j'ai des doutes sur la syntaxe :
'How do I create a Range of rows to delete for Criteria1 Not Equal to
"F1PPM60601" OR F1PPM60549" Etc...
'
Range("a2").AutoFilter Field:=1,
Criteria1:="<""F1PPM60601"",""F1PPM60549"",""F1PP M60623"""
.
I have serious doubts on my Range Criteria Syntax
Have a good day,
J.P.

How do I Delete Rows on Range Criteria for Column A Cells Not Equal to specific values ?
 

Hi J.P.,

Am Sat, 25 Apr 2015 20:51:04 +0200 schrieb Claus Busch:

Sub DeleteRows()

a better way:

Sub DeleteRows2()
Dim strCheck As String
Dim LRow As Long, i As Long
Dim varData As Variant

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

strCheck = "F1PPM60601,F1PPM60549,F1PPM60623"

With Sheets("Invoices")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:A" & LRow)
For i = UBound(varData) To LBound(varData) Step -1
If InStr(strCheck, varData(i, 1)) = 0 Then
Rows(i + 1).Delete
End If
Next
End With

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional