create array from range values VBA

Question

i'm trying to do a macro where i will need an array of values. this array is created from a range which will be variable in size. i've tried the below but keep having an error:

Sub chase()


Dim rng As Range
Dim myarray() As Variant

last = Range("r1").End(xlDown).Address
MsgBox last

myarray = Range("r1:" & last).Value
For i = LBound(myarray) To UBound(myarray)
        msg = msg & myarray(i) & vbNewLine
    Next i
MsgBox "the values of my dynamic array are: " & vbNewLine & msg


End Sub

any idea how to process?

thanks

A style note - I'm a BIG fan of picking the column, and going UP from the bottom of the column, as opposed to starting at the top and going DOWN - this will let you find the bottom, even if there's a gap in the middle of your data. — Selkie
– Selkie, Commented Oct 24, 2018 at 16:09

score 3 · Accepted Answer · 2018-10-24 08:46:14Z

3

Use the last row number in the range assignment.

dim last as long

last = Range("r1").End(xlDown).row
myarray = Range("r1:r" & last).Value

You're creating a 2-D array. Although some of the functions default to the first rank, it is better to treat a 2-D array as a 2-D array.

For i = LBound(myarray, 1) To UBound(myarray, 1)
        msg = msg & myarray(i, 1) & vbNewLine
Next i

edited Oct 24, 2018 at 8:46

answered Oct 24, 2018 at 8:40

user4039065

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

Comments

SNicolaou · Accepted Answer · 2018-10-24 08:44:39Z

1

See below:

Sub chase()

  Dim i As Integer, msg As String
  Dim last As Variant
  Dim rng As Range
  Dim myarray As Variant

  last = Sheet1.Range("r1").End(xlDown).Address
  MsgBox last

  myarray = Sheet1.Range("r1:" & last).Value
  For i = LBound(myarray) To UBound(myarray)
    msg = msg & myarray(i, 1) & vbNewLine
  Next i

  MsgBox "the values of my dynamic array are: " & vbNewLine & msg

End Sub

answered Oct 24, 2018 at 8:44

SNicolaou

5501 gold badge5 silver badges15 bronze badges

2 Comments

SNicolaou Over a year ago

Make sure you prefix your range with the sheet that is referring to avoid unexpected results.

SNicolaou Over a year ago

your array is two dimensional (x,y) so you need to be indexing the second dimension too.

Collectives™ on Stack Overflow

create array from range values VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related