Previous Topic Next Topic

Copying a row based on part of a field

Solved/Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 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

Answer 1 / 1
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
 490
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
Ask a question