How about something like:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range

Dim DoNotUseWks As Worksheet
Dim NoFormulasWks As Worksheet

Dim RngToDelete As Range

Set DoNotUseWks = Worksheets("do not use")
Set NoFormulasWks = Worksheets("ReArranged - No Formulas")

With DoNotUseWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With NoFormulasWks
'don't do all the rows--just the ones with data
Set myRng = .Range("N1", .Cells(.Rows.Count, "N").End(xlUp))

For Each myCell In myRng.Cells
'if the cell is empty, it'll have .value = 0, too.
If myCell.Value = 0 Then
If .Cells(myCell.Row, "Y").Value <= 1 Then
myCell.EntireRow.Cut _
Destination:=DestCell
Set DestCell = DestCell.Offset(1, 0)

'do you want to delete that cut row?
If RngToDelete Is Nothing Then
Set RngToDelete = myCell
Else
Set RngToDelete = Union(myCell, RngToDelete)
End If
End If
End If
Next myCell
End With

'if you do want to clean up those cut cells
If RngToDelete Is Nothing Then
'nothing to delete
Else
RngToDelete.EntireRow.Delete
End If

End Sub

I added a couple of statements that allow you to delete the cut rows--if you
want to keep those rows, then delete those lines or comment them out.



drdavidge wrote:

hey guys, is there a better way to do something like this? i have a for
statement checking an entire column for a value of zero, and if it
finds that value, i want it to check a different column for a value of
<= 1, and if it finds that, cut the row out and paste it somewhere
else. heres my (horrible) code:

Sheets("ReArranged - No Formulas").Select
For Each rng In Range("N:N")
If rng.Value = 0 Then

For Each rng2 In Range("Y:Y")
If rng2.Value <= 1 Then
rng2.EntireRow.Cut Destination:=Sheets("DO NOT USE").Range("A" &
Sheets("DO NOT USE").Rows.Count).End(xlUp).Offset(1, 0)
End If
Next rng2

End If
Next rng

--

Dave Peterson