Retaining Rows based on Array Value
I wouldn't use Autofilter for this:
Option Explicit
Sub DeleteRow2()
Dim Rng As Range
Dim iRow As Long
Dim myArr as variant
dim res as variant
dim FirstRow as long
dim LastRow as long
myArr = array("VAL1", "VAL2", "VAL3")
with activesheet
firstrow = 1
lastrow = .cells(.rows.count,"E").end(xlup).row
for irow = lastrow to firstrow step -1
res = application.match(.cells(irow,"E").value, myArr,0)
if isnumeric(res) then
'found it, do nothing
else
'not in myarr
.rows(irow).delete
end if
next irow
end with
End Sub
cdelfino wrote:
Below codes was a modified version that i got here to delete rows
based on the ColVal array...however need to revised this that instead
of deleting/removing rows based on the ColVal values, they should be
the one retained not removed. How can I do that?? Any help is
appreciated...
ColVal = Array( _
"VAL1", "VAL2", "VAL3", _
)
DeleteRow_Autofilter ColVal, LastRow
---
Sub DeleteRow_Autofilter(ColVal As Variant, LastRow As Long)
Dim Rng As Range
Dim I As Long
For I = LBound(ColVal) To UBound(ColVal)
ActiveSheet.Range("E1:E" & LastRow).AutoFilter Field:=1,
Criteria1:=ColVal(I)
With ActiveSheet.AutoFilter.Range
Set Rng = Nothing
On Error Resume Next
Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not Rng Is Nothing Then Rng.EntireRow.Delete
End With
Next I
ActiveSheet.AutoFilterMode = False
End Sub
--
Dave Peterson
|