VBA Trick of the Week :: Slicing an Array Without Loop – Application.Index

Posted: June 12, 2013 by Transformer in Excel, VBA
Tags: , , ,

VBA Trick HatSometimes we need to slice an array i.e. fetch a row/column from a multidimensional array. There is no inbuilt function in VBA to do the same and the most common way to do so is using a loop. However it can be done using a worksheet function named Index.

Syntax :

  Application.Index(Array, Row_Number, Column_Number)
OR
  Application.WorksheetFunction.Index(Array, Row_Number, Column_Number)

To extract a column from the source array, ‘0’ should be passed as row_number argument. Similarly, to extract a row from source array, ‘0’ should be passed as column_number argument.

e.g.

Sub Test()

    Dim varArray            As Variant
    Dim varTemp             As Variant

    varArray = ThisWorkbook.Worksheets("Sheet1").Range("A1:E10")
    varTemp = Application.Index(varArray, 2, 0)

End Sub

In the above example second row of varArray will be fetched in varTemp. We can also extract more then one row/column at the same time. In this case an array of numbers (indicating row/column) should be passed as row_number / column_number argument.

e.g. If in the above example we need to extract 2nd, 4th and 5th rows then row number would be passed as an array:

varTemp = Application.Index(varArray, Array(2, 4, 5), 0)

Similarly, to extract columns:

varTemp = Application.Index(varArray, 0, Application.Transpose(Array(2, 4, 5)))

This function can also be used to fill values in a particular row/column of a range.

Syntax : Application.Index(Range, Row_number , Column_number) = SourceArray/Range

In the same example to fill the values of 2nd column of varArray to 2nd column of range [A1:E10], we would do the following

Application.Index([A1:E10], , 2) = Application.Index(varArray, , 2)
Advertisements
Comments
  1. snb says:

    Why didn’t you post my feedback ?

  2. its a good idea to use index to extract elements from array
    but it would work if Ubound 1 is not more then 65536, and rows and column which we are going to extract must not contain any value which len is more then 255

  3. […] the near future I will be having a closer look at: VBA Trick of the Week :: Slicing an Array Without Loop – Application.Index from Useful […]

  4. When I read this this morning, I thought “when am I ever going to need that?”. Then I used it just a few hours later. http://stackoverflow.com/questions/17151886/how-to-read-e-e-as-the-same-thing-using-search-macro-in-excel/17155284#17155284

    Good tip.

  5. […] screenshot below shows Index used to return the 4th row and the 3rd column of the sample data (see Useful Gayaan for more […]

  6. Very nice, thanks for sharing

  7. hivishy says:

    It works as read/write on range. How can we use it to write to a row or column of an array ?

  8. Transformer says:

    in case of array we can not write to a row or column.

  9. desilussioned with Application.Index says:

    I’m trying to replicate your example here, but whenever you try to extract more than one row or one column, the method described fails. It only extracts a one-dimensional array with the i-th element specified in the Array(2,4,5) -In other words, the second, fourth and fifth element of the first column or row you specified

    • Alan Elston says:

      Hi “desilussioned with Application.Index says:“
      .. maybe my reply July 2 2015 is what you want???

  10. desilussioned with Application.Index says:

    Oh, btw, in your example, varTemp = Application.Index(varArray, 2, 0) does not return the second column, it actually returns the second row. Just clarifying…

  11. Sam says:

    There is a method that is way easier.

    If you are tryıng to find a variable, such as productname, you can do the following:

    Cells.Find(What:=productname, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    Do While (ActiveCell.Value productname)
    Cells.FindNext(After:=ActiveCell).Activate
    Loop

    myRow=activecell.row
    myColumn=activecell.column

    • MaxFraudy says:

      i’m trying to understand how your code does the same thing as is done in the post here. I can’t find any similarity.
      The post tells about working with an array. We are not trying to ‘find’ any value.
      Or did I misinterpret you?

      • Philip Augustus says:

        This seems really cool, but is not working for me. Is it possible that this only works when varArray is a range, and not an arbitrary array?

  12. MaxFraudy says:

    @Philip: it should be working for any type of array. can you share your code with us. you can email it to mistertransformer@gmail.com and we can get back to you.

  13. Mark Moore says:

    I to am unable to get this to work when I a want to slice more than one column to a new array. The below code was written to test this and have added comments to show the results for different line items

    Sub TestSlicing()
    Dim MainArray As Variant
    Dim SlicedArray As Variant
    ‘This is a dynamic named range that is read in to produce a 2D array currently 48 rows, by 4 columns
    MainArray = Application.Names(“RequestPrintLookup”).RefersToRange.Value
    ‘The below justs tests out the array is working as expected and puts out elements 1 and 4 for the 4th record
    MsgBox MainArray(4, 1) & ” ” & MainArray(4, 4)

    ‘——example 1 – working as expected – slices column 4 into the sliced array ——–
    SlicedArray = Application.Index(MainArray, 0, 4)
    ‘Writes out for confirmation the ubound for first dimension, is 48 as expect
    MsgBox UBound(SlicedArray, 1), , “Example 1 – Ubound of element 1”
    ‘Writes out for confirmation the thirtieth element – results are as expect
    MsgBox SlicedArray(30, 1), , “Example 1 – Results of sliced array, showing 30th element”

    ‘———–example 2 not workign as epected, desire is to take columns 3 and 4 from mainarray
    SlicedArray = Application.Index(MainArray, 0, Application.Transpose(Array(3, 4)))
    ‘Writes out for confirmation the ubound for first dimension, now only shows 2 instead of 48 expected
    MsgBox UBound(SlicedArray, 1), , “Example 2 – Ubound of element 1)”
    ‘Writes out for confirmation the thirtieth element – throws “Subscript out of range as there are only
    ‘2 rows of data, not the expected 48
    MsgBox SlicedArray(30, 1), , “Example 2 – Results of sliced array, showing 30th element”

    End Sub

    • DiGiMac says:

      Like Desilussioned and Mark Moore I am also unable to replicate your example to extract more than one column.
      Also, why use the transpose function?

      • Alan Elston says:

        Hi DiGiMac
        .. maybe my reply July 2 2015 is what you want ? – it gives another alternative for picking out specific rows and columns from an Array
        .. my code works as it is , and does not work without the transpose function fort he wanted Column indices. I am also struggling to understand how / why on that one!! : Maybe someone like Transformer could explain or do a good blog on that one?
        Alan

  14. jose luis (Spain) says:

    i found it really interesting. thanks a lot.

  15. Ches says:

    I’m getting run-time error 13, type mismatch. I define my variables like so:

    dim data as variant
    dim rng as range
    set rng = worksheets(1).range(“A1:B2”)
    data = rng.value2

    And then if I type the following in the immediate window:

    ?application.index(data, 1, 0)

    I get run-time error 13.

    • Transformer says:

      application.index(data, 1, 0) will return an array and can not be printed in immediate window.However, you can print its elements like shown below:
      e.g. ?Application.Index(data, 1, 0)(1)
      ?Application.Index(data, 1, 0)(2)

  16. BMG says:

    I have also tried to access multiple columns in an array to copy into a sheet range. The method seems to work fine for one column, but fails with several.
    Using application.index(thearray,,array(1,3,5)) returns the first row only and repeats it as someone else noted earlier. Real shame. Wanted to avoid looping.

    • Alan Elston says:

      Hi BMG,
      ..Re ” to access multiple columns in an array to copy into a sheet range. “, maybe my reply July 2 2015 is what you want ? – it gives another alternative for picking out specific rows and columns from an Array
      . Alan Elston

  17. Lukas says:

    Nice Tipp! I deal with this situation rather often, but hadn’t thought of doing this before. Thanks!

  18. Alan Elston says:

    Do you know if there are speed advantages ( Or disadvantages ) of this method over a simple looping routine ?

  19. NPueyo says:

    I am also having problems with this

    I have (i.e.) this range:
    0 10 20 30 40
    2 12 22 32 42
    4 14 24 34 44
    6 16 26 36 46
    8 18 28 38 48
    10 20 30 40 50
    12 22 32 42 52
    14 24 34 44 54
    16 26 36 46 56
    18 28 38 48 58

    and my code is:

    Sub TEST()
    Dim varArray As Variant
    Dim varTemp As Variant

    varArray = Range(“A1:E10”)
    varTemp = Application.Index(varArray, 1, 0)
    Range(“F14:J14”) = varTemp
    End Sub

    the result:
    0 10 20 30 40

    Also works fine if I get a column

    BUT if i try to get more columns
    changing varTemp = Application.Index(varArray, Array(1, 2, 5, 6, 8), 0)

    The result is a row:
    0 2 8 10 14

    • Transformer says:

      Right. Need to check for more than columns/rows.

    • Alan Elston says:

      Hi NPueyo,
      . I am not sure if this helps: .. But if You are trying to get an Array, or Range output of the rows 1, 2, 5, 6, and 8, then this code will do it.

      Sub TESTIE()
      10 Dim varArray As Variant
      20 Dim varTemp As Variant
      30 Dim rws() As String: Let rws() = Split(“1 2 5 6 8″, ” “)
      40 Dim clms() As Variant: Let clms() = Application.Transpose(Array(1, 2, 3, 4, 5))

      50 varArray = Range(“A1:E10”)
      60 varTemp = Application.Index(varArray, rws(), clms())
      70 varTemp = Application.Transpose(Application.Index(varArray, rws(), clms()))
      80 Range(“F14”).Resize(UBound(varTemp, 1), UBound(varTemp, 2)) = varTemp
      End Sub

      . For your data it gives in Range(“F14:J18”) this:

      0 10 20 30 40
      2 12 22 32 42
      8 18 28 38 48
      10 20 30 40 50
      14 24 34 44 54

      . Similarly changing line 40 to this,

      40 Dim clms() As Variant: Let clms() = Application.Transpose(Array(1, 5))

      would give an output of all the rows 1, 2, 5, 6, and 8 , but only give the first and last column in Range(“F14:G18”):

      0 40
      2 42
      8 48
      10 50
      14 54

      …………………
      . So you see you can use the code to pick out whichever combination of columns and rows you wish

      . I found this out by trial and error whilst answering a Forum Thread. I could not find any written explanation of how the index is working here. So I attempted an explanation myself Here: ( Around Post #13 )
      http://www.mrexcel.com/forum/excel-questions/858046-visual-basic-applications-copy-data-another-workbook-based-criteria.html?s=7982a41b9f65830d627de0e137c8210d#post4174322

      . Hope that may be of some help to you

      Alan Elston
      Germany

      • Alan Elston says:

        P.s.
        . The code I wrote above will error if you want the output of just one row. (Because line 70 gives a one dimensional Array by virtue of the fact that the transpose function working on a 2 dimensional 1 column Array returns a one dimensional Array.) So lines from 80 would need to be replaces either by the following codes lines , or by replacing the .Transpose with a true Function to Transpose such as described here:
        http://www.excelforum.com/excel-new-users-basics/1080634-vba-1-dimensional-horizontal-and-vertical-array-conventions-ha-1-2-3-4-a-2.html#post4094754
        ….
        . Modified code:

        80 On Error Resume Next ‘This Error handler surpresses any error – ( No “exception is raised” ) and the program contiunues after the line that would have errored
        90 If UBound(varTemp, 2) 0 so we come here ( and do nothing ). Redundant code
        120 End If
        130 Range(“F14”).Resize(UBound(varTemp, 1), UBound(varTemp, 2)) = varTemp ‘This line will give the full output except for the case that varTemp is a one dimensional Array, in which case this line will error and not be carried out by virtue of the Error Handler
        140 On Error GoTo 0 ‘Good practice to use this Statement to turn off ( disable) the current error handler. This is all that is needed in the case of the Error Handler On Error resume Next as no exception has been raised
        End Sub

        . Hope I have not confused the issue too much.
        .
        . Alan

    • Alan Elston says:

      . Modified code Again as it did not copy correctly:

      80 On Error Resume Next ‘This Error handler surpresses any error – ( No “exception is raised” ) and the program contiunues after the line that would have errored
      90 If UBound(varTemp, 2) 0 so we come here ( and do nothing ). Redundant code
      120 End If
      130 Range(“F14”).Resize(UBound(varTemp, 1), UBound(varTemp, 2)) = varTemp ‘This line will give the full output except for the case that varTemp is a one dimensional Array, in which case this line will error and not be carried out by virtue of the Error Handler
      140 On Error GoTo 0 ‘Good practice to use this Statement to turn off ( disable) the current error handler. This is all that is needed in the case of the Error Handler On Error resume Next as no exception has been raised
      End Sub

    • Alan Elston says:

      My last two modified codes would not copy properly and lines 100 – 110 are missing?
      . contact me here if you want a copy
      Doc.AElstein@t-online.de

    • Alan Elston says:

      Ahh….. Problem was greater than and less than symbols which may have been taken as HTML. So code from line 80 again a bit modified … try again

      ‘The following lines could be replaced by just Line 130 for most cases….
      80 On Error Resume Next ‘This Error handler surpresses any error – ( No “exception is raised” ) and the program contiunues after the line that would have errored: http://www.exce
      90 If UBound(varTemp, 2) = 0 Then ‘This will error for the case when varTemp is a one dimensional Array, in which case the next line will be carried out by virtue of the Error Handler
      100 Range(“F14”).Resize(1, UBound(varTemp)) = varTemp ‘This line will give the first row output for the case of varTemp is a one dimensional Array
      110 Else ‘Either line 90 errored for the case of a 1 dimensional array, so the last line is carried out, Or it did not error in the case of a 2 dimensional array, but did not have a column Upper bound of 0 so we come here ( and do nothing ). Redundant code
      120 End If
      130 Range(“F14”).Resize(UBound(varTemp, 1), UBound(varTemp, 2)) = varTemp ‘This line will give the full output except for the case that varTemp is a one dimensional Array, in which case this line will error and not be carried out by virtue of the Error Handler
      140 On Error GoTo 0 ‘Good practice to use this Statement to turn off ( disable) the current error handler. This is all that is needed in the case of the Error Handler On Error resume Next as no exception has been raised
      End Sub

  20. burak says:

    Hi, thanks for the very useful trick. I wonder if is there a way to slice columns something like:

    varTemp = Application.Index(varArray, 0, Application.Transpose(Array(2 to 5))) or

    varTemp = Application.Index(varArray, 0, Application.Transpose(Array(2:5)))

    (unfortunately the two exp. above are not working)

    instead of writng one by one like
    varTemp = Application.Index(varArray, 0, Application.Transpose(Array(2, 3, 4, 5)))

    • Alan Elston says:

      See my reply 2 July 2015. With the second argument as a 1 column 2 dimensional array of all row indicies, and the third arguments as a 1dimensional Array of the column indicies that you want it should work as you wish
      Alan

    • Alan Elston says:

      see also
      http://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html

      so for your example, your second argument would look like:

      Evaluate(“column(B:E)”)

      • burak says:

        Hi Alan,

        I guess i couldn’t explained well enough. I have an array called

        Arr1(1 to 51, 1 to 13) as double

        which is created through looping within an excel vba module by some mathematical calculations with option base1.

        The first column consists of ID numbers and the next 25 columns are the results that i am seeking for the max and min values where i am trying to get without looping if possible.

        Result_Max = WorksheetFunction.Max(Application.Index(Arr1, , Application.Transpose(clms))

        works like a charm if i set

        clms() = Array(2, 3 , 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25)

        which is very cumbersome especially when i consider that the Arr1 boundaries are dynamic and set by the user to adjust the precision.

        So, my main problem is that i couldn’t find an easy way to create such clms() array of which elements should be between 2 dynamic integers such as 2 to 25 as given above.

  21. Alan Elston says:

    Hi burec.
    Thanks for the Feedback.

    OK , … I thought it might be something along those line….

    The following may still be a bit cumbersome. But maybe you could simplify it and possibly turn it into a function etc…

    This will give you your required 1D Array based on the given Upper and lower Column Numbers in variables LB and UB
    It converts the column Numbers into the corresponding column Letter.
    Then it uses the Method I indicated before in Post 19 and 20 November to give you the sequential numbers you require

    Sub burakGenerateSequentialColumnIndiciesFromLetters() ‘Dec 9 usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/
    ‘Variables for…
    Dim LB As Long, UB As Long ‘…User Given start and Stop Column as a Number
    Let LB = 2: Let UB = 25
    Dim strLtrLB As String, strLtrUB As String ‘…Column Letter corresponding to Column Number
    ‘There are many ways to get a Column Letter from a Column Number – excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html
    Let strLtrLB = Split(Cells(1, LB).Address, “$”)(1) ‘An Address Method
    Let strLtrUB = Replace(Replace(Cells(1, UB).Address, “1”, “”), “$”, “”) ‘A Replace Method
    ‘Obtain Column Indicies using Spreadsheet Function Column via VBA Evaluate Method
    Dim clms() As Variant
    Let clms() = Evaluate(“column(” & strLtrLB & “:” & strLtrUB & “)”) ‘Returns 1 D “pseudo” Horizontal Array of sequential numbers from column number of LB to UB
    ‘Or
    clms() = Evaluate(“column(” & Split(Cells(1, LB).Address, “$”)(1) & “:” & Replace(Replace(Cells(1, UB).Address, “1”, “”), “$”, “”) & “)”)
    End Sub

    Alan Elston

  22. Michal says:

    Application.Index was unstable for me – I used it a lot in one project and ended up having to transform all instances to resizing via loop. I don’t think it’s the 255 characters limit or 65556 rows – my arrays were usually about 1000×3 with 4 – 10 characters in the fields. Excel would sometimes ! break and if you press F5 then it would run further with no problem. Posting it only as a warning, I don’t have exact reason for the issue, just posting as a warning as I couldn’t find any similar problems on google

  23. Sharad says:

    How to assign a variable in place of ’10’ in “Application.Index([A1:E’10’], , 2) = ” ?

    • Alan Elston says:

      ‘I do not think you can do this using the [ ] shorthand. I may be wrong.
      ‘But this demonstrate Different Cuntstructions of First Argument in .Index
      Sub IndexFieldReturnDiffCuntFistArgWonk()
      Dim vTemp As Variant ‘Variable constructed to accept all info allowing its use for all other variables with the exceptiion of a defined String length greater than 255 http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
      Dim FieldReturned As Variant ‘Variable constructed to accept all info allowing its use for all other variables with the exceptiion of a defined String length greater than 255
      Dim arr() As Variant ‘Variable to accept a Field ( Array ) of Variant Types to suit that returned when .Index has first argument ( grid ) of Varaint Types
      ‘ Hard Coding
      Let FieldReturned = Application.Index([A1:E10], 0, 2) ‘Using named range
      Let FieldReturned = Application.Index(Range(“A1:E10”), 0, 2) ‘Using Spreadsheet range
      Let arr() = Application.Index(Evaluate(“If(Row(),A1:E10)”), 0, 2) ‘Using Evaluated spreadsheet values
      ‘ “Soft” Coding
      Dim Rw As Long ‘This variable Type can be fixed from the outset*** to specific memory space: Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there’s no point using anything but Long.–upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
      Let Rw = 10 ‘ The Value of the Variable has changed. The “size” ( Memory required space ) has not***
      Let FieldReturned = Application.Index(Range(“A1:E” & Rw & “”), 0, 2) ‘Range( Literal string required, which VBA recognises when in Paranthesis ” ” )
      Let arr() = Application.Index(Evaluate(“If(Row(),A1:E” & Rw & “)”), 0, 2) ‘In VBA ” & takes you out of and & ” back into the Literal String
      ‘ The [ ] works slightly differently. It brings you specifically in the Spreadsheet Range as a ( reserved ) name there http://www.mrexcel.com/forum/excel-questions/899117-visual-basic-applications-range-a1-a5-vs-%5Ba1-a5%5D-benefits-dangers-2.html#post4332606
      End Sub

    • Alan Elston says:

      ‘I do not think you can do this using the [ ] shorthand. I may be wrong.
      ‘But this demonstrate Different Contstructions of First Argument in .Index
      Sub IndexFieldReturnDiffContFistArgWonk()
      Dim vTemp As Variant ‘Variable constructed to accept all info allowing its use for all other variables with the exceptiion of a defined String length greater than 255 http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
      Dim FieldReturned As Variant ‘Variable constructed to accept all info allowing its use for all other variables with the exceptiion of a defined String length greater than 255
      Dim arr() As Variant ‘Variable to accept a Field ( Array ) of Variant Types to suit that returned when .Index has first argument ( grid ) of Varaint Types
      ‘ Hard Coding
      Let FieldReturned = Application.Index([A1:E10], 0, 2) ‘Using named range
      Let FieldReturned = Application.Index(Range(“A1:E10”), 0, 2) ‘Using Spreadsheet range
      Let arr() = Application.Index(Evaluate(“If(Row(),A1:E10)”), 0, 2) ‘Using Evaluated spreadsheet values
      ‘ “Soft” Coding
      Dim Rw As Long ‘This variable Type can be fixed from the outset*** to specific memory space: Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there’s no point using anything but Long.–upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
      Let Rw = 10 ‘ The Value of the Variable has changed. The “size” ( Memory required space ) has not***
      Let FieldReturned = Application.Index(Range(“A1:E” & Rw & “”), 0, 2) ‘Range( Literal string required, which VBA recognises when in Paranthesis ” ” )
      Let arr() = Application.Index(Evaluate(“If(Row(),A1:E” & Rw & “)”), 0, 2) ‘In VBA ” & takes you out of and & ” back into the Literal String
      ‘ The [ ] works slightly differently. It brings you specifically in the Spreadsheet Range as a ( reserved ) name there http://www.mrexcel.com/forum/excel-questions/899117-visual-basic-applications-range-a1-a5-vs-%5Ba1-a5%5D-benefits-dangers-2.html#post4332606
      End Sub

  24. Alan Elston says:

    ‘I do not think you can do this using the [ ] shorthand. I may be wrong.
    ‘But this demonstrate Different Contstructions of First Argument in .Index
    Sub IndexFieldReturnDiffContFistArgWonk()
    Dim vTemp As Variant ‘Variable constructed to accept all info allowing its use for all other variables with the exceptiion of a defined String length greater than 255
    Dim FieldReturned As Variant ‘Variable constructed to accept all info allowing its use for all other variables with the exceptiion of a defined String length greater than 255
    Dim arr() As Variant ‘Variable to accept a Field ( Array ) of Variant Types to suit that returned when .Index has first argument ( grid ) of Varaint Types
    ‘ Hard Coding
    Let FieldReturned = Application.Index([A1:E10], 0, 2) ‘Using named range
    Let FieldReturned = Application.Index(Range(“A1:E10”), 0, 2) ‘Using Spreadsheet range
    Let arr() = Application.Index(Evaluate(“If(Row(),A1:E10)”), 0, 2) ‘Using Evaluated spreadsheet values
    ‘ “Soft” Coding
    Dim Rw As Long ‘This variable Type can be fixed from the outset*** to specific memory space: Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there’s no point using anything but Long.–upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
    Let Rw = 10 ‘ The Value of the Variable has changed. The “size” ( Memory required space ) has not***
    Let FieldReturned = Application.Index(Range(“A1:E” & Rw & “”), 0, 2) ‘Range( Literal string required, which VBA recognises when in Paranthesis ” ” )
    Let arr() = Application.Index(Evaluate(“If(Row(),A1:E” & Rw & “)”), 0, 2) ‘In VBA ” & takes you out of and & ” back into the Literal String
    ‘ The [ ] works slightly differently. It brings you specifically in the Spreadsheet Range as a ( reserved ) name there
    End Sub

  25. Alan Elston says:

    ‘ Ref Variant FieldReturned arr() http://www.eileenslounge.com/viewtopic.php?f=27&t=22512

  26. Alan Elston says:

    Hi
    Coming right back to the original codes in this Blog. It might be worth noting that the slicing method actually will work on a Range Object and, importantly return a Range Object for the case of returning a single Row ( or column ). One can then, if only values are wanted apply the .Value property to that returned Range Object to return the same Array of Variant types as in the Array case. But one has the extra flexibility of having in the first instance the range object from which ant to which other Methods can be applied. ( And one does not have the 255 x 65535 limits )
    Compare the following code with very original code in this blog. Lines 70 will produce the same Array as the original VarTemp at the start of this Blog.
    ( Note also my alternative method of Line 90 will not return a Range, but the identical Array is returned in Line 100 )

    Further more I have been finding in general that the .Index ( as with .Match ) are slightly quicker when using the form such as in Lines 130 and 135 to return an Array. I expect that may be VBA does some internal conversion of an Array as First Argument in .Index ( Second Argument in .match ) into a “pseudo” Range. These are after all “Worksheet” Functions..

    Alan

    Ref: http://www.eileenslounge.com/viewtopic.php?f=30&t=22787&p=177079#p177072

    Sub SliceRange()
    10 Dim rngIn As Range
    20 Set rngIn = Range(“A1:E10”)
    30
    40 Dim rngOut As Range
    50 Set rngOut = Application.Index(rngIn, 2, 0) ‘Returns Range Object Range(“A2:E2”)
    60 Dim VarTemp() As Variant ‘ Array to accept Variant Type Fiels Elements returned by applying .Value Property to a Range of more than one cell
    70 Let VarTemp() = rngOut.Value ‘Returns 2 D Array ( 1 to 1, 1 to 5 )
    80
    90 ‘Set rngOut = Application.Index(rngIn, 2, Array(1, 2, 3, 4, 5)) ‘Runtime Error 424 – Object required
    100 Let VarTemp() = Application.Index(rngIn, 2, Array(1, 2, 3, 4, 5)) ‘Returns 1 D Array ( 1 to 5 ), same values as Line 70
    110
    120 Dim vTemp As Variant
    130 Let vTemp = Application.Index(rngIn, 2, 0) ‘Returns 2 D Array ( 1 to 1, 1 to 5 ) identical to line 70
    135 Let vTemp = Application.Index(rngIn, 2, 0).Value ‘Returns 2 D Array ( 1 to 1, 1 to 5 ) identical to line 70
    End Sub

  27. Konstantin says:

    If multiple rows need to slice, this is how it works:

    With Application
    varTemp = .Transpose(varArray, _
    Array(2,4,5), _
    .Transpose(Array(1,2,3…n))))
    End With

    when n – is number of columns in varTemp

    • Alan Elston says:

      This version of your code snippet has a syntax error

      With Application
      VarTemp = .Transpose(varArray, _
      Array(2, 4, 5), _
      .Transpose(Array(1, 2, 3))))
      End With

      This does not have a syntax error, but does not appear to work

      With Application
      VarTemp = .Transpose(varArray, _
      Array(2, 4, 5), _
      .Transpose(Array(1, 2, 3)))
      End With

      _………………………………………………………..

      Possibly you meant this:

      With Application
      VarTemp = .Transpose(.Index(varArray, _
      Array(2, 4, 5), _
      .Transpose(Array(1, 2, 3))))
      End With

      The above gives the same results as the following, which is the form I have discussed a few times in this Blog

      With Application
      VarTemp = .Index(varArray, _
      Application.Transpose(Array(2, 4, 5)), _
      (Array(1, 2, 3)))
      End With

      Your ( corrected ) version is OK, but has an extra unecerssary .Transpose
      _……….

      Alan

  28. Alan Elston says:

    Hi
    One quick follow up here….. Following a discussion from Rick here:
    http://www.mrexcel.com/forum/general-excel-discussion-other-questions/929381-visual-basic-applications-split-function-third-argument-refers-maximum-outputs-%93when-splitting-stops-%94.html#post4467983
    I can see a more pleasantly looking version of my basic code to get selected Rows and columns…
    Rather than this:
    VarTemp() = Application.Index(varArray, Application.Transpose(Array(2, 4, 5)), (Array(1, 2, 3)))
    You can use this to achieve the same
    VarTemp() = Application.Index(varArray, [ { 2 ; 4 ; 5 } ], [ { 1 , 2, 3 } ])

    The key is the
    [ { 2 ; 4 } ]
    Representing a 2 Dimension “1 Column” Array.
    This possibility is overlooked sometimes maybe, as often we see more often the “1 Dimension” or “row” form represented by things like
    = Array(1, 2)
    And
    { 1 , 2 }

    Alan

  29. Lucas says:

    Hi Alan, I’m trying to apply your code but I’m finding that there is no output, all “#VALUE!”, so presumably I’m using it incorrectly. My current code is as follows:

    Dim vLoadData As Variant, vNewData As Variant

    Dim rws() As String: Let rws() = Split(“1 2 3 4 5”, “”)
    Dim clms() As Variant: Let clms() = Application.Transpose(Array(2, 20, 6, 28))
    vLoadData = Range(Cells(1, iSameDate), Cells(28, iEndDate))
    vNewData = Application.Index(vLoadData, rws(), clms())
    vNewData = Application.Transpose(Application.Index(vLoadData, rws(), clms()))

    Range(Cells(1, iUpdateCell), Cells(5, iUpdateCell + iEndDate – iSameDate)) = vNewData

    Is there anything obvious I am missing/doing wrong?

    The “i…” variables are all integers that refer to columns (numerically) in the spreadsheet.

    • Alan Elston says:

      Hi Lucus,
      A few things:

      _1) Please Try to give a full code and reduced size Data, as it makes it easier for me to work on for you.

      _2) You are not using my most recent codes. You are using variations possibly of earlier ones. Take a look again through my Posts…..
      _ …. I found later that you can do away with one Transpose. The General Formula then is now
      arrOut() = Application.Index(arrIn(), rwsT(), clms()) ‘ rwsT() is a 2 Dimension 1 Column Array , clms() is a 1 Dimensional Array.
      ( _ I use rwsT() currently to remind me that this is a Transpose of rws(), ( if rws() is a 1 Dimension Array ) )

      _3) I do not quite understand the followoing Line, ….
      Range(Cells(1, iUpdateCell), Cells(5, iUpdateCell + iEndDate – iSameDate)) = vNewData
      _ ……and I cannot check the code without having specific values of your “i…” variables…….

      _ 3a) Note: Using my codes, you obtain a vNewData which is a new Array reduced in size compared with the original Data Array vLoadData.
      _ So, Usually if I chose for example cell AG6 as the Top left of where I want my Output to go, then the Line to output to the Spreadsheet would have the following Form.
      Range(“AG6”).Resize(UBound(vNewData, 1), UBound(vNewData, 2)).Value = vNewData
      Here the .Resize Property is used to return a new Range increased to the size of the output Array vNewData. So then this new size has the correct size to match to the output Array vNewData. The values of the output Array vNewData can then be assigned to the Spreadsheet Range.
      _……………………………………………

      Here are some modified versions of your Code. They all appear to work.
      I am guessing a bit exactly what you want. I am guessing that you want to have in your output Array, vNewData, is the rows, 1 , 2 , 3, 4, 5 and columns 2, 20, 6, 28 from your Input Array, vLoadData.

      Sub Lucas_1() ‘ usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/comment-page-1/#comment-599
      Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(“NPueyoGyanArraySlicing”) ‘ !!CHANGE SHEET NAME TO SUIT YOURS!!
      Dim iSameDate As Integer, iEndDate As Integer, iUpdateCell As Integer
      Let iSameDate = 1: Let iEndDate = 28: Let iUpdateCell = 6
      Dim vLoadData As Variant, vNewData As Variant
      Dim rws() As String: Let rws() = Split(“1 2 3 4 5″, ” “)
      Dim clms() As Variant: Let clms() = Application.Transpose(Array(2, 20, 6, 28))
      vLoadData = ws.Range(ws.Cells(1, iSameDate), ws.Cells(28, iEndDate))
      vNewData = Application.Index(vLoadData, rws(), clms())
      vNewData = Application.Transpose(Application.Index(vLoadData, rws(), clms()))
      ‘Range(Cells(1, iUpdateCell), Cells(5, iUpdateCell + iEndDate – iSameDate)) = vNewData
      Range(“AG6”).Resize(UBound(vNewData, 1), UBound(vNewData, 2)).Value = vNewData
      End Sub

      Sub Lucas_2()
      Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(“NPueyoGyanArraySlicing”) ‘ !!CHANGE SHEET NAME TO SUIT YOURS!!
      Dim iSameDate As Long, iEndDate As Long, iUpdateCell As Long ‘ ( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there’s no point using anything but Long.–upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
      Let iSameDate = 1: Let iEndDate = 28: Let iUpdateCell = 6
      Dim vLoadData() As Variant, vNewData() As Variant
      Dim rws() As String: Let rws() = Split(“1 2 3 4 5″, ” “)
      Dim rwsT() As Variant: Let rwsT() = Application.Transpose(rws())
      ‘Or simpler for continuous rows giving Directly a 2 D 1 column Array
      Let rwsT() = Evaluate(“=” & “Row(1:5)”)
      Dim clms() As Variant: Let clms() = Array(2, 20, 6, 28)
      vLoadData() = ws.Range(ws.Cells(1, iSameDate), ws.Cells(28, iEndDate))
      vNewData() = Application.Index(vLoadData(), rwsT(), clms())
      Range(“AG6”).Resize(UBound(vNewData, 1), UBound(vNewData, 2)).Clear
      Range(“AG6”).Resize(UBound(vNewData, 1), UBound(vNewData, 2)).Value = vNewData
      End Sub
      Sub Lucas_3()
      Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(“NPueyoGyanArraySlicing”) ‘ !!CHANGE SHEET NAME TO SUIT YOURS!!
      Dim iSameDate As Long, iEndDate As Long, iUpdateCell As Long
      Let iSameDate = 1: Let iEndDate = 28: Let iUpdateCell = 6
      Dim vLoadData() As Variant, vNewData() As Variant
      vLoadData() = ws.Range(ws.Cells(1, iSameDate), ws.Cells(28, iEndDate))
      vNewData() = Application.Index(vLoadData(), [{ 1;2;3;4;5 }], [{ 2 , 20, 6, 28 }])
      Range(“AG6”).Resize(UBound(vNewData, 1), UBound(vNewData, 2)).Clear
      Range(“AG6”).Resize(UBound(vNewData, 1), UBound(vNewData, 2)).Value = vNewData
      End Sub

      _…………………………………………….

      P.s. One Final Note: When you copy from this Blog , sometimes Quotes “ “ ‘ and minus signs – – copy incorrectly, so you may need to delete those and retype them in.

      Alan

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s