EXCEL VBA Store / Save entire row in an array?

Question

Have recieved help how to search in a column for a string, and then store it in an array. Is it possible to store the entire Row? Have searched for it but can't find it.

I want to search in one Sheet that contains data by a string. Then copy those rows that contains that string to another sheet.

My code looks like this.

Set wsRaw = Worksheets("raw_list")
Set phaseRange = wsRaw.Columns(PhaseCol)

SearchString = "start"
Set aCell = phaseRange.Find(What:=SearchString, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
    Set bCell = aCell
    ReDim Preserve arrStart(nS)
    arrStart(nS) = aCell.Row
    nS = nS + 1
    Do While ExitLoop = False
        Set aCell = phaseRange.FindNext(After:=aCell)
        If Not aCell Is Nothing Then
            If aCell.Row = bCell.Row Then Exit Do
            ReDim Preserve arrStart(nS)
            arrStart(nS) = aCell.Row
            nS = nS + 1
        Else
            ExitLoop = True
        End If
    Loop
Else
End If

Thankfull for any help :)

Answer 1

Since you are copying data from Sheet1 to Sheet2 based on your search criteria in a relevant column then then I would suggest using Autofilter.

See this

Sub Sample()
    Dim wsRaw As Worksheet
    Dim strSearch As String
    Dim PhaseCol As Long, LastRow As Long
    Dim phaseRange As Range, rng As Range

    strSearch = "start"

    '~~> Change this to the relevant column
    PhaseCol = 1

    Set wsRaw = Sheets("raw_list")

    With wsRaw
        LastRow = .Range(Split(Cells(, PhaseCol).Address, "$")(1) & _
                  .Rows.Count).End(xlUp).Row

        Set phaseRange = wsRaw.Range( _
                                    Split(Cells(, PhaseCol).Address, "$")(1) & _
                                    "1:" & _
                                    Split(Cells(, PhaseCol).Address, "$")(1) & _
                                    LastRow)

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> Filter, offset(to exclude headers) and copy visible rows
        With phaseRange
            .AutoFilter Field:=1, Criteria1:=strSearch
            Set rng = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
            '~~> Chnage Sheet2 to the relevant sheet name where you want to copy
            rng.Copy Sheets("Sheet2").Rows(1)
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

SNAPSHOT