loop and search

[wutn]

Hi - I've been trying to write a macro that will cut and paste several rows from a workbook into another file based on a defined range of criterion. At the moment, I've managed to come up with the below, but I have several questions - hopefully some of the good folk here can assist..

Sub TESTRUN()

Dim Criteria As Range
Dim count As Integer
ThisWorkbook.Sheets("range").Activate
count = 2
Range("A2").Select
Criteria = Range(Selection, Selection.End(xlDown))
ActiveSheet.Paste
Workbooks.Open Filename:="q:\test.xls"
ActiveWorkbook.Sheets("STUFF").Activate
For i = 1 To 65536 Step 1
If Cells(i, 5).Value = "TARGET" Then
Cells(i, 1).EntireRow.Cut
Windows("results.xls").Activate
ActiveWorkbook.Sheets("Result").Activate
count = count + 1
Rows(count).Select
ActiveSheet.Paste
Windows("test.xls").Activate
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub

a) When I run the above code, for some reason it skips adjacent rows next to each other even if they match my criterion. For instance, if in my test.xls I have data in rows 10 and 11 containing the value "TARGET" in column E, the macro seems to skip row 11. It seems to only occur when there are adjacent rows - I have no idea why.

b) could someone suggest a more efficient way of writing this instead of simply shuffling between windows? or any other general improvements? (is there a way to cut, paste, and delete a target row without having to shuffle back and forth the way I've done up there?

c) as you can see I've defined a range "Criteria" above - but I don't know how to construct a loop that will search each row in test.xls for any row which matches any of the values in "Criteria" (it's just one column of text values manually updated by the user - and these text values only need to be searched for in one column along any given row in test.xls.

Would greatly appreciate help as my sanity is on the line here... thanks!

[SPDavern]

The reason that your code seems to be skipping row 11 is that you're using the cut entire row method:

Cells(i, 1).EntireRow.Cut

to extract the data you've found. The cut move all the rows up one, so when the index in increased by 1 (from 10 to 11) it compares to the old row 12. Row 12 became row 11 when you cut the entire row out. You could copy the row over to the other sheet instead of cutting and pasting.

You will speed up the execution of your code by turning off the screen updating during the copy and pasting. Use:

Application.ScreenUpdating = False

at the beginning of your macro and then return it to True at the end of your macro. You won't be able to see it work through the data but it will run much faster.

You might consider using the For Each loop and next the screening of each criteria inside the loop moving down the column of data.

Hope that helps,
Sean

[wutn]

Hi...

I'm still getting the same problem as above - ie. target rows that are adjacent to each other get ignored. Can someone suggest an amendment to the code below? Or am I doing something horribly wrong?

Thanks!

Code as follows:


Sub TESTRUN()

'
Application.ScreenUpdating = False
ThisWorkbook.Sheets("range").Activate
Dim a As Range
Dim count As Integer
count = 2
Range("A2").Select
Set a = Range(Selection, Selection.End(xlDown))
Workbooks.Open Filename:="test.xls"
ActiveWorkbook.Sheets("data").Activate
For i = 1 To 65536 Step 1
For Each Cell In a
If Cells(i, 31).Value = a.Cells Then
Cells(i, 1).EntireRow.Copy
Windows("results.xls").Activate
ActiveWorkbook.Sheets("Result").Activate
count = count + 1
Rows(count).Select
ActiveSheet.Paste
Windows("test.xls").Activate
Cells(i, 1).EntireRow.Delete
End If
Next Cell
Next i
Application.ScreenUpdating = True
End Sub

[SPDavern]

The line of code just before the "end if" that deletes the entire row is causing the same problem I described before. It deletes row i which makes row i+1 become row i. Then without checking the new row i it cycles to the next i skips to the next row.