Slice specific non-adjacent columns from array

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Slice specific non-adjacent columns from array

Post by YasserKhalil »

Hello everyone
I have two sheets.. In sheet1 I have data in range("A1:K6")
I need to slice specific columns from sheet1 and put them in sheet2 ...the columns are 2,7,11
I tried this but can't figure it out

Code: Select all

Sub Test()
    Dim arr, temp
    Dim i As Long
    
    arr = Sheets("Sheet1").Range("A1").CurrentRegion.Value
    'temp = Application.Index(arr, Array(2, 7, 11), 0)
    'temp = Application.Index(arr, , Application.Transpose(Array(2, 7, 11)))
    
    Sheets("Sheet2").Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = Application.Index(arr, , Array(2, 7, 11))
End Sub
Note : I can do it using traditional copy and past but I need to learn how to slice the columns from the array itself
Thanks advanced for help

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Slice specific non-adjacent columns from array

Post by HansV »

I don't see the point, but you could do it like this:

Code: Select all

Sub Test()
    Dim arr
    Dim i
    Dim j As Long
    Dim n As Long
    
    arr = Sheets("Sheet1").Range("A1").CurrentRegion.Value
    n = UBound(arr, 1)
    
    For Each i In Array(2, 7, 11)
        j = j + 1
        Sheets("Sheet2").Cells(1, j).Resize(n).Value = Application.Index(arr, , i)
    Next i
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Slice specific non-adjacent columns from array

Post by YasserKhalil »

Thank you very much for this fantastic reply. now I realized that it can't be done in one shot with index
I was imagining to store the multiple non-adjacent columns from the array into another array using index

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Slice specific non-adjacent columns from array

Post by Doc.AElstein »

It might just be of passing interest to add here, that the “slicing” technique demonstrated by Hans in his last code is an interesting case where you can get .Index to return you a Range Object. In most cases .Index returns a value or Array of values. By default, as in the case of the code example used by Han’s, the “Slicing” technique will also return an Array of values.

But by carefully Dim ing variables, you can get the code to return a Range Object:

The following code does the same as Han’s code. But it returns a Range.
Not only is this advantageous in allowing you to have Full Range Objects returned, but also, even if you are finally interested in only values, the following code allows you to overcome the various size limits and other problems associated with Arrays used in Worksheet Functions such as .Index

Code: Select all

 Sub TestRngversion() '   http://www.eileenslounge.com/viewtopic.php?f=30&t=25517
    Dim arr
    Dim i
    Dim j As Long
    Dim n As Long
Dim Rngarr As Range
 Set Rngarr = Sheets("Sheets1").Range("A1").CurrentRegion
 Let n = Rngarr.Rows.Count
    
    For Each i In Array(2, 7)
        j = j + 1
Dim rngReturn As Range
 Set rngReturn = Application.Index(Rngarr, 0, i)
        Sheets("Sheet2").Cells(1, j).Resize(n) = rngReturn
    Next i
End Sub
'



' Rem Ref Slicing: https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/#comment-587
' Rem Ref Array problems when using worksheet functions:  http://excelmatters.com/2016/03/08/transpose-bug-in-2013-and-2016/
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Slice specific non-adjacent columns from array

Post by YasserKhalil »

Thanks lot Mr. Alan for your reply and solution. It is very interesting too.
Best and kind regards