SearchString,copy and paste in next sheet

[Francois084]

Good day, All
I am using this code to look for specific Like value in a column then copy it over to next sheet and then remove the row
but when it goes to copy the next row it skips a row and start but the row below

Please Help.

Much thanks in advance

Sub SearchString()

Dim c As Range
Dim Source As Worksheet
Dim Target As Worksheet
Dim Target1 As Worksheet
Dim rowIterator As Long
Set Source = ActiveWorkbook.Worksheets("Master List")
Set Target = ActiveWorkbook.Worksheets("Group")

For Each c In Source.Range("E1:E" & Source.Cells(Rows.Count, "E").End(xlUp).Row)

If c Like "*FOR*" Then
c.EntireRow.copy
Target.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
c.EntireRow.Delete
ElseIf c Like "*+*" Then
c.EntireRow.copy
Target.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
c.EntireRow.Delete
End If
Next c

End Sub

 

[Joe4]

Welcome to the Board!

There is always danger when trying to loop through a range, but then you are either deleting or inserting rows into that range as you are looping through it.
This can cause certain rows to be skipped (as deleting a row moves all the rows below that up one) or certain rows to be hit twice (as inserting a row moves all rows down one).

It is much better to loop through the range backwards to avoid those issues.

So you can find the last row with data in the column you are working with, and then loop through them like this:

For r = lastRow to 1 Step -1

and then refer to your ranges within your loop like this:

Cells(r, "E")

or

Range("E" & r)

 

[Francois084]

Thank you
Sorry to ask!
Could you please explain more or give an example or where r should be.
I only start VBA this week

 

[Joe4]

Maybe something like this (untested):

Sub SearchString()

Dim lr As Long
Dim r As Long
Dim c As Range
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("Master List")
Set Target = ActiveWorkbook.Worksheets("Group")

'   Find last row in column E on Source sheet
    lr = Source.Cells(Rows.Count, "E").End(xlUp).Row

'   Loop through each row in column E backwards
    For r = lr To 1 Step -1
'       Build "c" range
        Set c = Source.Range("E" & r)
'       Proceed with rest of your code
        If c Like "*FOR*" Then
            c.EntireRow.Copy
            Target.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            c.EntireRow.Delete
        ElseIf c Like "*+*" Then
            c.EntireRow.Copy
            Target.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            c.EntireRow.Delete
        End If
    Next r

End Sub

Note that I documented the code to explain what is going on in the steps I created.

 

[Francois084]

Thank you very much.
It worked very nicely.

 

[Joe4]

You are welcome.

I hope it makes sense the issue with trying to loop through a range going forward as you are messing with the size of the range.
If you weren't deleting any rows from your original range, your loop would work fine, as it you had it originally written.