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 :)

In the above code you are storing the row number where a match is found. Why do you want to store the entire row contents in the array. What exactly are you trying to achieve? — Siddharth Rout
– Siddharth Rout, Commented Jul 5, 2012 at 8:04
In one sheet i have these "cases" that are in different phases. Each case is one Row. So that I want to achieve is to search that sheet for a certain phase and then store those cases that are in that phase in an array if it is possible, so i can call for them later. Sorry for my bad english and lack to inform what i mean. — Andreas
– Andreas, Commented Jul 5, 2012 at 8:17
In that case you don't need to store the entire row :) You can store the Row numbers as you are doing and then later on retrieve the row details from the row numbers. — Siddharth Rout
– Siddharth Rout, Commented Jul 5, 2012 at 8:19
Wich function would I use to do that? And thanks again for your quick replies. — Andreas
– Andreas, Commented Jul 5, 2012 at 8:22
To retrieve the rows data you can use this Sheets("Sheet1").Rows(arrStart(i)) — Siddharth Rout
– Siddharth Rout, Commented Jul 5, 2012 at 8:31

Siddharth Rout · Accepted Answer · 2012-07-05 09:11:15Z

4

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

enter image description here

answered Jul 5, 2012 at 9:11

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andreas Over a year ago

Thanks for your answer. But let's say hypothetically that I would continue to use my arrays. Is it possible to check the row you have searched up and then copy certain columns to another sheet?

Siddharth Rout Over a year ago

Yes :) Sheets("Sheet1").Rows(arrStart(i)).Copy Sheets("Sheet2").Rows(r)

Andreas Over a year ago

I sense I do something terribly wrong :) I have succeded to write out the array in a different sheet. But when I try to copy the rows, only the last row appears.

code For i = 1 To 1000  Sheets("raw_list").Rows(arrStart(i)).Copy  Sheets("DataSheet").Rows (r)  Sheets("DataSheet").Select Range("A1").Select  ActiveSheet.Paste Application.CutCopyMode = False  Next

Andreas Over a year ago

Never mind :) I found where I ha done wrong. Thank ou so much for your help

Collectives™ on Stack Overflow

EXCEL VBA Store / Save entire row in an array?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related