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

Question

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

Report

Erenagh - Aug 18, 2011 at 07:07 AM
TrowaD

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

- Aug 18, 2011 at 10:43 AM

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.

Copying a row based on part of a field
Copying cond. format for entire row based on 1 cell to other row ✓ - Forum - Excel
Excel formula copy rows based on criteria ✓ - Forum - Excel
Excel formula to duplicate rows based on cell value ✓ - Forum - Excel
Create rows based on cell value - Forum - Excel
Excel copy row based on cell value ✓ - Forum - Excel

1 reply

Ask a question

TrowaD · Answer 1 · 2011-08-18T10:43:21-0400

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

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

1 reply

Your reply

Subscribe To Our Newsletter!