CODEWITHSUNDEEP
arraysexcelvba
Rere
Rere

Reputation: 23

Storing data to array from worksheet

I'm trying to analyze some data from a worksheet, the first step was to find the last row, which I managed. Then I need to store the data in an array for each column to simplify further analysis.

My data looks like this:

Data picture

I'm trying to store let's say the B column in an array but starting at B6:

Sub List_Rem_stock()  

    Dim array_Rem_Batch(1 To last_row_Rem_stock - 5) As Integer

    For i = 1 To last_row_Rem_stock - 5
        array_Rem_Batch(i) = Worksheets("Rem stock").Range(Bi)
    Next i
    Debug.Print array_Rem_Index

End Sub

last_row_Rem_stock represents the last row of the table. Am I doing this properly?

Upvotes: 2

Views: 239

Answers (2)

FAB
FAB

Reputation: 2569

You can allocate a range to an array (2D) as such:

Dim arrData as variant: arrData = Range("B1:B" & lastrow).

You can also put the array back on the spreadsheet the same way:

Range("B1:B" & lastrow) = arrData

Simple, easy and fast, without the need of iterating through data.

In your example, you would probably do it like this.

Sub List_Rem_stock()

    Dim i As Long, last_row_Rem_stock As Long
    Dim array_Rem_Batch As Variant
    
    With Worksheets("Rem stock")
        last_row_Rem_stock = .Cells(.Rows.Count, "B").End(xlUp).Row 'get last row in B
        array_Rem_Batch = .Range("B1:B" & last_row_Rem_stock)
    End With

    For i = 6 To last_row_Rem_stock
         Debug.Print array_Rem_Batch(i, 1)
    Next i

End Sub

To note that arrays allocated this way will always start at 1, not 0.

EDIT:

I'm allocating the data starting at row 1, and not at row 6, purely for the nice 1:1 relation between array index and sheet rows. Is my prefered way, wherever the situation allows.

    If array_Rem_Batch(i, 1) = Range("B" & i) Then ....

Can always allocate the data from any row you want:

    array_Rem_Batch = Worksheets("Rem stock").Range("B6:B100") 'now the array has 95 rows.

In this case, array index 1, will corespond to row 6 in the sheet, and will have to manage this in the code if you need to something like this:

    If array_Rem_Batch(i, 1) = Range("B" & i + 5) Then ....

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

Almost, try the code below (find explanation inside code's comments):

Option Explicit

Sub List_Rem_stock()

Dim last_row_Rem_stock As Long, i As Long
Dim array_Rem_Batch() As Long

With Worksheets("Rem stock")
    last_row_Rem_stock = .Cells(.Rows.Count, "B").End(xlUp).Row ' get last row with value in colum B

    ReDim array_Rem_Batch(1 To last_row_Rem_stock - 5) ' redim array size

    For i = 1 To last_row_Rem_stock - 5
        array_Rem_Batch(i) = .Range("B" & i).Value
    Next i
End With

End Sub

Upvotes: 1

Related Questions