Extract a single column data from an array

[tiredofit]

In Sheet1 I have 10 columns of data.


I want to extract only the second column, so have used this:

    Dim TotalArray() As Variant


    TotalArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
    
    Dim NewArray() As Variant
    
    NewArray = Application.Index(TotalArray(), 0, 2)

With only a few rows of data, it works as expected.

However when I have a few thousand rows, it fails with this message:

Run-time error 13


Type mismatch

Is this a well known problem with lots of data?



Thanks

 

[DanteAmor]

Your code works well with 300,000 records.
But Try this, it works with 300,000 records too.

    Dim TotalArray As Range
    Dim NewArray As Variant


    Set TotalArray = Sheet1.Cells(1, 1).CurrentRegion
    NewArray = TotalArray.Columns(2)

 

[tiredofit]

Thanks

 

[DanteAmor]

You're welcome. Thanks for the feedback.