2 Dimensional Array from Non Sequential Columns

[mabbutt]

Hi,

I would like to create a 2-dimensional array from two columns that are not next to each other.

This works for when they are next to each other e.g. A,B F,G etc but I would like to have columns D,P for example:

    lr = .Cells(.Rows.Count, 6).End(xlUp).Row
    arr = .Range("F1:G" & lr)

I have looked at disjoint arrays and several other examples but cannot get the code to work at all.

I have also tried to build the range using this variation:

    lr = .Cells(.Rows.Count, 6).End(xlUp).Row
    arr = .Range("F1", "H" & lr)

Again I can get this method to work but only if the columns are side by side.

 

[Rick Rothstein]

Give this a try (no need for the lr variable)...

Arr = Application.Index(Cells, Evaluate("ROW(1:" & Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row & ")"), Array(4,16))

If you want to preserve the lr variable, then...

lr = .Cells(.Rows.Count, 6).End(xlUp).Row
Arr = Application.Index(Cells, Evaluate("ROW(1:" & lr & ")"), Array(4,16))

 

[mabbutt]

Give this a try (no need for the lr variable)...

Arr = Application.Index(Cells, Evaluate("ROW(1:" & Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row & ")"), Array(4,16))

If you want to preserve the lr variable, then...

lr = .Cells(.Rows.Count, 6).End(xlUp).Row
Arr = Application.Index(Cells, Evaluate("ROW(1:" & lr & ")"), Array(4,16))

Hi Rick,

I'm extremly sorry for the late reply as I have been unwell.

This is absolutely perfect and so simple.

Thank you!

 

[mohadin]

If you want to preserve the lr variable, then...

lr = .Cells(.Rows.Count, 6).End(xlUp).Row
Arr = Application.Index(Cells, Evaluate("ROW(1:" & lr & ")"), Array(4,16))

But Do you need to is it not at the end of the day lr=Ubound(Arr)??