m

two ways:

Sub Test2()
' start from the bottom
Dim LastRow As Long
Dim rwIndex As Long
LastRow = Range("A65536").End(xlUp).Row
For rwIndex = LastRow To 1 Step -1
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = "X") Then
Rows(rwIndex).Delete
End If
End With
Next 'rwIndex
End Sub

Sub Test3()
Dim LastRow As Long
Dim rwIndex As Long
Dim RangeToDelete As Range
LastRow = Range("A65536").End(xlUp).Row
For rwIndex = 1 To LastRow
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = "X") Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = _
ActiveSheet.Cells(rwIndex, 1)
Else
Set RangeToDelete = _
Union(ActiveSheet.Cells(rwIndex, 1), _
RangeToDelete)
End If
End If
End With
Next 'rwIndex
If Not RangeToDelete Is Nothing Then
RangeToDelete.EntireRow.Delete
End If
End Sub

Regards

Trevor


"m" wrote in message
...
I am working on a macro that will copy an entire row of
data onto another sheet based on a condition in the first
column. the only way i know how to do this is to copy the
entire source worksheet into a temp worksheet, tag each
row as either keep or delete, delete all of the rows that
i dont want, and then copy the remainder to destination
worksheet. here is what i am trying to use to delete the
rows:

For rwIndex = 1 To 1000
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = "X") Then
Rows(rwIndex).Delete
End If
End With
Next rwIndex

however, this formula does not work. i can get it to hide
the rows (hidden = ture) but not delete. any ideas? thanks