Selecting/Assigning multiple columns and rows to array

Question

I have column structure similar to the below one

   Author   Book
    ----    ----
    ----    ----
    Vicky   Book1
    Vicky   Book2
    Giri    Book1
    Giri    Book3

The author column is in column B and Book column might come in any column based on other column datas

I want to select values in these two columns

I tried

fileArray = Range("B4:B" & finalRow & "," & endColumn).Value

Here the first part is working fine, finalRow contains the row number till which record is there, and endColumn is where the book details will be available

If i use

fileArray = Range("B4:B" & finalRow )

it is getting values, but when i try to add one more column it is throwing error

Also i tried the below one too

fileArray = Range("B4:B9,S4:S9").Value

But it is not getting values of S4:S9

How can get the desired values?

Try fileArray = Range("B4:" & endColumn & finalRow). (Assuming that endColumn is a letter, not a number.) — nicolaus-hee
– nicolaus-hee, Commented Apr 28, 2015 at 5:51

L42 · Accepted Answer · 2015-04-28 07:36:08Z

1

You can do it like this:

Dim fileArray
Dim finalRow As Long
Dim targetCol As Long

With Sheets("SheetName") ' change to your actual sheet name
    finalRow = .Range("B" & .Rows.Count).End(xlUp).Row
    ' assuming "Book" is the column header and headers are in 3rd row
    ' find the correct column number
    targetCol = .Range("B3").EntireRow.Find("Book").Column
    fileArray = Array(.Range("B4:B" & finalRow), _
                .Range(.Cells(2, targetCol), .Cells(finalRow, targetCol)))
End With

To get the values:

Debug.Print fileArray(0)(1) ' returns Vicky
Debug.Print fileArray(1)(1) ' returns Author1

So it's like the 1st parenthesis enclosed number is the column number, the second is the row.

answered Apr 28, 2015 at 7:36

L42

19.8k11 gold badges47 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

Selecting/Assigning multiple columns and rows to array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related