for and if statement within another for/if statement

[drdavidge]

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

[drdavidge]

hmm.. i managed to change it to:

Please Login or Register  to view this content.

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

[Dave Peterson]

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

[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

[drdavidge]

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

Please Login or Register  to view this content.