Upload range into an array

[PCH]

I have a spreadsheet called, "Materials Codes and Prices.xls", with a sheet
named "Stock".
The sheet named Stock has Four columns containing:
Column A – Part numbers (integer)
Column B – Description (string)
Column C – Price (Double)
Column D – Weight (Double)

As new items are added to the list, the number of rows used is for ever
increasing.

How can I upload this ever increasing size of the range into an array into
another instance of Excel?

Thanks

Ashby

 

[Chip Pearson]

Ashby,

If you can configure your data such that it will always be bounded by an
empty row and an empty column, you can use the CurrentRegion property to get
the full range of your data.

Sub AAA()
Dim V As Variant
Dim NumRows As Long
Dim NumCols As Long
Dim R As Long
Dim C As Long

V = Range("StartCell").CurrentRegion
NumRows = UBound(V, 1) - LBound(V, 1) + 1
NumCols = UBound(V, 2) - LBound(V, 2) + 1
For R = 1 To NumRows
For C = 1 To NumCols
Debug.Print CStr(V(R, C))
Next C
Next R
End Sub

Another way is to use the End property to go from the last row in the sheet
(Rows.Count = 64K in most versions of Excel) upward to the last used cell in
a column, such as "D".

Sub BBB()
Dim V As Variant
Dim LastRow As Long
Dim LastCell As Range
Dim Rng As Range

Const C_FIRST_ROW = 1 '<<< CHANGE AS NEEDED
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(LastRow, "D"))
V = Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)