Hi all, how can I make array work in below macro

Sub del()
For Each c In Sheets("Data").Column("A")
If c.Value < Array("XX", "YY", "SS", "ZZ") Then
c.EntireRow.Delete
End If
Next
End Sub

please can any friend can help

Sub del()
For Each c In Sheets("Data").Column("A")
If UBound(Filter(Array("XX", "YY", "SS", "ZZ"), c.Value)) = -1 Then
c.EntireRow.Delete
End If
Next
End Sub



On Nov 12, 10:33*am, K wrote:
Hi all, how can I make array work in below macro

Sub del()
For Each c In Sheets("Data").Column("A")
If c.Value < Array("XX", "YY", "SS", "ZZ") Then
c.EntireRow.Delete
End If
Next
End Sub

please can any friend can help

You have to be careful when using the Filter function... it will register
true for partial matches. For example, if c.Value in your example contained
just the letter "X" or "Y" (or any other substring of one of the Array
elements), then it will return an upper bound not equal to -1.

--
Rick (MVP - Excel)


"JP" wrote in message
...
Sub del()
For Each c In Sheets("Data").Column("A")
If UBound(Filter(Array("XX", "YY", "SS", "ZZ"), c.Value)) = -1 Then
c.EntireRow.Delete
End If
Next
End Sub



On Nov 12, 10:33 am, K wrote:
Hi all, how can I make array work in below macro

Sub del()
For Each c In Sheets("Data").Column("A")
If c.Value < Array("XX", "YY", "SS", "ZZ") Then
c.EntireRow.Delete
End If
Next
End Sub

please can any friend can help

There are a few ways...

But before that, if you're deleting rows, it's better to start from the bottom
and work your way up. Otherwise, you'll delete a row and have to keep track of
where you are--or you'll miss processing a value (since the data shifted up a
row).

Another way is to build a range that should be deleted and then delete it all at
once (without addressing your question):

Option Explicit
Sub del()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("Data")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If somecondition Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

Option Explicit
Sub del2()

Dim myCell As Range
Dim myRng As Range
Dim DelRng As Range

With Worksheets("Data")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
If Condition Then
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
Next myCell

If DelRng Is Nothing Then
MsgBox "Nothing to delete"
Else
DelRng.EntireRow.Delete
End If

End Sub

==========
To address your question...

You have a few choices...

For iRow = LastRow To FirstRow Step -1
Select Case UCase(.Cells(iRow, "A").Value)
Case Is = "XX", "YY", "SS", "ZZ"
'do nothing
Case Else
.Rows(iRow).Delete
End Select
Next iRow

Or (this one had more changes, so I included the second version with all the
code):

Sub del2()

Dim myCell As Range
Dim myRng As Range
Dim DelRng As Range
Dim res As Variant 'could be an error
Dim myArr As Variant

With Worksheets("Data")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

myArr = Array("XX", "YY", "SS", "ZZ")

For Each myCell In myRng.Cells
res = Application.Match(myCell.Value, myArr, 0)
If IsNumeric(res) Then
'it matches something in the array, skip it
Else
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
Next myCell

If DelRng Is Nothing Then
MsgBox "Nothing to delete"
Else
DelRng.EntireRow.Delete
End If

End Sub

======
The other thing to watch out for...

You (probably) don't want to check the entire column (like:
Sheets("Data").Column("A"))

In xl2003, you'd be checking 64k cells. In xl2007, it would be a million cells.







K wrote:

Hi all, how can I make array work in below macro

Sub del()
For Each c In Sheets("Data").Column("A")
If c.Value < Array("XX", "YY", "SS", "ZZ") Then
c.EntireRow.Delete
End If
Next
End Sub

please can any friend can help

--

Dave Peterson