Partial array slice

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
Hi,

Have a 2D array R x C in size, C max (column count) = 14.

Is there syntax (without explicit ref e.g. arr(x,1) & arr(x,2) & ... & arr(x,8) ) to join first 8 columns per row?

Psuedo code:
Code:
myKey = LEFT(join(arr), 8)
where 8 represents 8th column (not 8th char!)

Ideally, without creating a function to mimic this either.

TIA,
Jack
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Something like
Code:
   ary = Range("A1:O20").Value2
   For i = 1 To UBound(ary)
      x = Join(Application.Index(ary, i, Array(1, 2, 3, 4, 5, 6, 7, 8)), ",")
   Next i
 
Upvote 0
Thank you for the replies.

Marcelo, unfortunately your suggestion returns a msgbox with lots of FALSEs!

Is it because you're slicing an entire column before transposing it? If so, I apologise for any confusion in explanation. I'd like to join the first 8 elements in a row of an array.


Fluff, yours is what I was after, though without writing out 1, 2, 3.. 8!
However, it works and seems like I can't avoid explicitly using 1, 2, 3... 8 (or via loop x = 1 to 8) & your suggestion slots into my existing code easy.

Thank you both for replying!



(Background, I have a horizontal calendar, 18 columns per month, which I convert into a flat-table (i.e. no gaps). Each value per calendar row turns into a "next" row on the flat table, hence needing a key when I turn flat-table back to the calendar view to detect when a "new" row on the calendar view is found)
 
Last edited:
Upvote 0
You could use another array for the columns you want like
Code:
   ary = Range("A1:O20").Value2
   Cols = Array(1, 2, 3, 4, 5, 6, 7, 8)
   For i = 1 To UBound(ary)
      x = Join(Application.Index(ary, i, Cols), ",")
   Next i
 
Upvote 0
Cool, thanks, understood your suggestion Fluff, I was just curious to see if there was syntax equivalence to "LEFT" or anyway to avoid explicitly listing all the column numbers out; after no joy searching online and seems not!
 
Upvote 0
Thank you for the replies.

Marcelo, unfortunately your suggestion returns a msgbox with lots of FALSEs!

Maybe I misunderstood what you want.
My test
Data

A
B
C
D
E
F
G
H
I
J
1
2
DataB1​
DataC1​
DataD1​
DataE1​
DataF1​
DataG1​
DataH1​
DataI1​
DataJ1​
3
DataB2​
DataC2​
DataD2​
DataE2​
DataF2​
DataG2​
DataH2​
DataI2​
DataJ2​
4
DataB3​
DataC3​
DataD3​
DataE3​
DataF3​
DataG3​
DataH3​
DataI3​
DataJ3​
5
DataB4​
DataC4​
DataD4​
DataE4​
DataF4​
DataG4​
DataH4​
DataI4​
DataJ4​
6
DataB5​
DataC5​
DataD5​
DataE5​
DataF5​
DataG5​
DataH5​
DataI5​
DataJ5​
7
DataB6​
DataC6​
DataD6​
DataE6​
DataF6​
DataG6​
DataH6​
DataI6​
DataJ6​
8

Code:
Sub aTest()
    Dim arr As Variant, myKey As Variant
    
    arr = Range("B2:J7")
    
    myKey = Join(Application.Transpose(Application.Index(arr, 0, 8)), ",")
    MsgBox myKey
End Sub

I've gotten this in Message Box
DataI1,DataI2,.....,DataI6

This is what you want, isn't?

M.
 
Upvote 0
As long as the columns are consecutive, you could use
Code:
      x = Join(Application.Index(ary, i, Evaluate("column(A:I)")), ",")
 
Upvote 0
Hi Marcelo, I think you did mis-understand, with Fluff's help I .. urgh did create a function to abstract it away from the main procedure!.

If my first row is: A, B, C, D, E, F, G, H, I J etc ,I wanted key to be "ABCDEFG"
Second row: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 => key "12345678"

The data is sorted, In my data, top 5 rows all have key: "ABCDEFG" but remaing columns have different date values (i.e. 5 different dates).

So for each data row, I need to map the value to a date but keep in the same row as the key as I turn the flat-table view, back to a calendar view - that's why I'm "splitting" my array by the 8th column. Hope this explains, it's just an annoying data translation I'm trying to figure out.
 
Upvote 0
Evaluate is word I seem to struggle with in VBA (not sure why!), though this I get - it's returning an array of (column) numbers that map to A:I.

And yes, the columns are consecutive :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top