Copying a row based on part of a field [Solved/Closed]

Report
-
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
-
Hello,

I am trying to get an excel macro to do a search of Sheet1 that will go through each row in column E and copy any row that contains a X or a 1Y. some examples that it will search through are 1M00774000, 1ML000000X, 1Y0025671Y. If anyone can direct me in this that would be great. Thank you! I have done this through the find function but I need it in a excel macro.

1 reply

Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
Hi Erenagh,

You didn't specify the destination for the copied rows. In the below code I copied them to sheet2 in the first available cell of column A.
Here is the code:
Sub Test()
Dim lRow As Integer
lRow = Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row
Set MR = Sheets("Sheet1").Range("E1:E" & lRow)
For Each Cell In MR
If (InStr(1, Cell.Value, "X") > 0) Or (InStr(1, Cell.Value, "1Y") > 0) Then
Cell.EntireRow.Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
Application.CutCopyMode = False
End Sub

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!