Copying all cells in a row until blank

[vmc62]

Hi,

I'm completely new to Macros-excel, and I was wondering if you could help me with this problem.

I have to search for a value on column A, the value is "EN". And once it's found, my code should copy its entire row until a blank cell. And paste it on another sheet.

Then go back, go one row below and do the same thing again.

So for example if my code looks like this in Sheet1:

HS | 14 | 15 | 16 | |
HS | 13 | 12 | 11 | |
EN | 18 | 15 | 20 | |
EN | 23 | 34 | 21 | |
RA | 21 | 32 | 21 | |
RA | 12 | 23 | 45 | |

After running the macros should look like this on sheet2:

HS | 14 | 15 | 16 | EN | 18 | 15 | 20 |
HS | 13 | 12 | 11 | EN | 23 | 34 | 21 |
RA | 21 | 32 | 21 | | | | |
RA | 12 | 23 | 45 | | | | |


This is the code I have so far, it's really nothing since I'm 100% new to this:

Sub SearchRow()

    
    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Range("A1").Activate

  'For t = 1 To 10 'This range can be changed as more rows are used.
     If ActiveCell.Value = "EN" Then
    
         Do
            ActiveCell.Offset(0, 1).Activate
            Selection.Copy
            
         Loop Until IsEmpty(ActiveCell)
         
         ActiveCell.Offset(0, 1).Activate
         Selection.Paste
          
      End If
     
      ActiveCell.Offset(1, 0).Activate
    
   'Next t
    
End Sub

Also I wanted to say that is not necessary to paste it all in Sheet2. If it's easier to do it all in the same Sheet1 (something like CUT and PASTE) that's fine, as long as it looks like the output I need.

Thank all of you in advance.

[vmc62]

Searching online, I just found a code that helped me accomplish what I needed. This is the code:

Sub Test()
    Dim Sh As Worksheet
    Dim Col As Long
    Dim RowFrom As Long
    Dim RowTo As Long
    Set Sh = Worksheets("Sheet1")
    Col = Sh.Cells(1, Sh.Columns.Count).End(xlToLeft).Column + 1
    RowFrom = 1
    RowTo = 1
    With Sh
        Do
            If IsEmpty(.Cells(RowFrom, 1)) Then Exit Sub
            If .Cells(RowFrom, 1).Value = "EN" Then
                With .Range(.Cells(RowFrom, 1), .Cells(RowFrom, Col - 1))
                    .Copy Sh.Cells(RowTo, Col)
                    .Delete Shift:=xlUp
                    RowTo = RowTo + 1
                End With
            Else
                RowFrom = RowFrom + 1
            End If
        Loop
    End With
    
End Sub

Now I'm trying to understand better the dynamic of what the code did, because I need to generalize it.

So for example If after the first 'shift', I needed to do another shift (this time looking for "RA"), that is:

Now,

From:

HS | 14 | 15 | 16 | EN | 18 | 15 | 20 |
HS | 13 | 12 | 11 | EN | 23 | 34 | 21 |
RA | 21 | 32 | 21 |
RA | 12 | 23 | 45 |
CS | 15 | 18 | 32 |
CS | 21 | 14 | 16 |

To:

HS | 14 | 15 | 16 | EN | 18 | 15 | 20 | RA | 21 | 32 | 21 |
HS | 13 | 12 | 11 | EN | 23 | 34 | 21 | RA | 12 | 23 | 45 |
CS | 15 | 18 | 32 |
CS | 21 | 14 | 16 |

What would I have to add to the code?

If someone could help me, I'd appreciate it.

Regards,

Victor

[royUK]

Try this

Option Explicit

Sub SearchRow()

    Dim rng    As Range
    Dim cl     As Range
    With Worksheets("Sheet1")
        Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight))
        Set cl = rng.Find(What:="EN", LookIn:=xlValues)
        If Not cl Is Nothing Then Range(cl, cl.End(xlToRight)).Copy _
           Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
End Sub