Results 1 to 3 of 3

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

  1. #1
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0

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

    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

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by princ_wns View Post
    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...
    Code:
    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.
    Last edited by Rick Rothstein; 05-23-2013 at 01:05 PM.

  3. #3
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    or
    Code:
    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

Similar Threads

  1. Replies: 1
    Last Post: 03-30-2013, 11:48 PM
  2. Combining data of Two Array or Range
    By princ_wns in forum Excel Help
    Replies: 5
    Last Post: 10-01-2012, 06:52 PM
  3. Replies: 3
    Last Post: 04-08-2012, 09:44 AM
  4. Assign an event to chart on workbook open
    By LalitPandey87 in forum Excel Help
    Replies: 2
    Last Post: 02-20-2012, 07:43 AM
  5. COUNTIF on Non-contiguous Range
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-23-2011, 04:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •