How To Assign A Non-contiguous Discontinuous Range To An Array

[princ_wns]

Hi All,

I have a range of 10 columns and i want to assign only columns 1,2,3,7,8,10 into an array. I tried it by using union on this range and selecting only thoes columns which are required but in array i am getting only columns 1,2,3. Please help me how can i do this as i have onther way of doing this copy thease columns on temp sheet and then assign this range into a array but i dont want to use any temp sheet.


Regards
Prince

[Rick Rothstein]

I have a range of 10 columns and i want to assign only columns 1,2,3,7,8,10 into an array. I tried it by using union on this range and selecting only thoes columns which are required but in array i am getting only columns 1,2,3. Please help me how can i do this as i have onther way of doing this copy thease columns on temp sheet and then assign this range into a array but i dont want to use any temp sheet.

Consider filling your array this way...

Dim LastRow As Long, vArray As Variant
Const FirstRow As Long = 2
Const WrkSht As String = "Sheet1"
'
'
'
LastRow = Worksheets(WrkSht).Columns("A:J").Find(What:="*", SearchOrder:=xlRows, _
          SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
vArray = Application.Index(Worksheets(WrkSht).Cells, Evaluate("Row(" & _
         FirstRow & ":" & LastRow & ")"), Split("1 2 3 7 8 10"))
'
'  vArray is a two-dimensional array where both dimensions are one-based... so
'  the value in cell G8 is located at vArray(7, 4) within the array where I point
'  out the FirstRow of data was assumed to be Row 2 (hence 7 instead of 8)
'

Note: The text string containing your column numbers (highlighted in red) is single-space delimited.

[snb]

or

Sub M_snb()
    Cells(1).CurrentRegion.Name = "snb_000"
    sn = Application.Index([snb_000], [row(snb_000)], Array(1, 2, 3, 7, 8, 10))
End Sub