Get columns from array VBA

Question

I'm having difficulty returning a set of columns from an array. I've returned an sql select statement to my variant array. It has 16 columns. I need to grab 12 of the 16 columns to return to my spreadsheet from a specific row in the array. I'm using this code to get my row:

    If UBound(Filter(budget, Cells(i, 1).Value, , vbBinaryCompare)) >= 0 And Cells(i, 1).Value <> "" Then

How do I then get just the 12 columns I need? The columns are the final 12 columns in the array and will always be in the order I need them.

Thanks in advance for the help!

This begs the question, why are you returning 16 columns when you only need 12? — Mathieu Guindon
– Mathieu Guindon, Commented Jun 10, 2016 at 18:49
a couple of reasons: I'm grouping by one of the columns my my sql statement, and I am using the other three columns to return aggregated data in other locations of the tool. — ye-olde-dev
– ye-olde-dev, Commented Jun 10, 2016 at 19:14

user6432984 · Accepted Answer · 2016-06-10 19:22:43Z

1

That's going to depend on the shape of the array.
If it's a one dimension array

 a = Filter(Selection, Cells(i, 1).Value, , vbTextCompare)
 rows = UBound(a)
 Cells(i, 1).Resize(1, rows ) = Application.WorksheetFunction.Transpose(a)

Notice that WorksheetFunction.Transpose(a) swaps the rows and columns of the array.

Multi-dimensional arrays will depend on how they where created.

We can think of it like this

Database Query Arrays: a = recordset.getRows()

And

Dim a(10, 100) Redim Preserve a(10, Ubound(a) + 1)

Are aligned like this a(columns, rows) because you can only re-size the last dimentsion of an array.
So we would: a = Filter(Selection, Cells(i, 1).Value, , vbTextCompare) rows = UBound(a, 2) columns = UBound(a, 1) Cells(i, 1).Resize(columns, rows ) = Application.WorksheetFunction.Transpose(a)

Excel Range Arrays are base 1 and have the same shape as the Range itself.

a = Range("A1:K200").Value
a(1,1) = cells(1, 1) evaluates to True

So you can do this

a = Range("A1:K200").Value
Range("A1:K200") = a

Or a = Filter(Selection, Cells(i, 1).Value, , vbTextCompare) rows = UBound(a, 1) columns = UBound(a, 2) Cells(i, 1).Resize(rows, columns) = a

answered Jun 10, 2016 at 19:22

user6432984

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

1 Comment

ye-olde-dev Over a year ago

ahhhh transposing is a great idea, I hadn't even though of that. thank you!

Collectives™ on Stack Overflow

Get columns from array VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related