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

hmm.. i managed to change it to:


Code:
--------------------

'Cut out rows
Sheets("ReArranged - No Formulas").Select
For Each rng In Range("N:N")
If rng.Value = 0 And rng.Offset(0, 11).Value <= 1 Then
rng.EntireRow.Cut Destination:=Sheets("DO NOT USE").Range("A" & Sheets("DO NOT USE").Rows.Count).End(xlUp).Offset(1, 0)
End If
Next rng

--------------------


but this seems like it is taking way too long. is there a faster way to
do something similiar?


--
drdavidge
------------------------------------------------------------------------
drdavidge's Profile: http://www.excelforum.com/member.php...o&userid=36168
View this thread: http://www.excelforum.com/showthread...hreadid=561608

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

In xl2003 and below, you'll be processing 64k rows. In xl2007, you'll be doing
a million rows.

I like to start at the bottom and find the last used row and stop processing
after that.

(see that other reply)


drdavidge wrote:

hmm.. i managed to change it to:

Code:
--------------------

'Cut out rows
Sheets("ReArranged - No Formulas").Select
For Each rng In Range("N:N")
If rng.Value = 0 And rng.Offset(0, 11).Value <= 1 Then
rng.EntireRow.Cut Destination:=Sheets("DO NOT USE").Range("A" & Sheets("DO NOT USE").Rows.Count).End(xlUp).Offset(1, 0)
End If
Next rng

--------------------

but this seems like it is taking way too long. is there a faster way to
do something similiar?

--
drdavidge
------------------------------------------------------------------------
drdavidge's Profile: http://www.excelforum.com/member.php...o&userid=36168
View this thread: http://www.excelforum.com/showthread...hreadid=561608

--

Dave Peterson

thanks for the guidance. i actually wound up trying this out instead and
it worked pretty quick:


Code:
--------------------

Dim lastRow2
lastRow2 = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row

For Q = 1 To lastRow
If Range("N" & Q).Value = 0 And Range("Y" & Q).Value <= 1 Then
Range("N" & Q).EntireRow.Cut Destination:=Sheets("DO NOT USE").Range("A" & Sheets("DO NOT USE").Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Q

--------------------


--
drdavidge
------------------------------------------------------------------------
drdavidge's Profile: http://www.excelforum.com/member.php...o&userid=36168
View this thread: http://www.excelforum.com/showthread...hreadid=561608