+ Reply to Thread
Results 1 to 10 of 10

Select & Copy Non-Contiguous Cells

  1. 11-23-2016, 10:04 PM #1
    Ichigo
    Ichigo is offline
    Registered User
    Join Date
    03-06-2009
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    47

    Select & Copy Non-Contiguous Cells

    Hi Friends of Excel Forum,

    I'm trying to copy Non-Contiguous Cells which may be 1 row or more. I'm not good in writing VBA codes, so I recorded the following:
    Please Login or Register  to view this content.
    This seemed to work for 1 row only. What if I select A1:A10 and I need it to copy A1:D10 together with G1:G10?

    Thanks.
    Last edited by Leith Ross; 11-24-2016 at 06:15 AM. Reason: Assed Code Tags
    Reply With QuoteRegister To Reply
  2. 11-23-2016, 10:34 PM #2
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Select & Copy Non-Contiguous Cells

    Quote Originally Posted by Ichigo View Post
    What if I select A1:A10 and I need it to copy A1:D10 together with G1:G10?
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important
    Reply With QuoteRegister To Reply
  3. 11-24-2016, 11:38 PM #3
    Ichigo
    Ichigo is offline
    Registered User
    Join Date
    03-06-2009
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    47

    Re: Select & Copy Non-Contiguous Cells

    Hi karedog,

    Thank you for your reply. I'm trying to further the selection to be able to select:

    1. Multiple rows
    2. 2 successive columns after the offset.

    I tried using your codes and edited as follows:

    Sub CopyNonContinuous()

    Range(Selection.Resize(1, 3).Address & "," & Selection.Offset(, 4).Address).Copy

    End Sub

    I tried to resize after the offset but due to my limited knowledge in VBA, none of them worked. Hope to get further help on this.
    Thanks.
    Reply With QuoteRegister To Reply
  4. 11-25-2016, 12:35 AM #4
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Select & Copy Non-Contiguous Cells

    You are welcome.
    I don't quite catch what you want, but I try to explain with this example :

    Suppose the current activecell is on A1, and you want to select A1:A3 and E1:F3

    Please Login or Register  to view this content.
    As as start, the Selection refer to A1, so to refer A1:A3, we must enlarge by 3 rows --> Selection.Resize(3).Address

    The second range (E1:F3), the top left cell of this second range is E1, we locate this cell from Selection by offsetting 4 columns,
    so E1 = Selection.Offset(, 4)
    Now to enlarge E1 to E1:F3, we use .resize method --> Selection.Offset(, 4).Resize(3, 2).Address (enlarge by 3 rows and 2 columns)
    Reply With QuoteRegister To Reply
  5. 11-25-2016, 05:09 AM #5
    Ichigo
    Ichigo is offline
    Registered User
    Join Date
    03-06-2009
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    47

    Re: Select & Copy Non-Contiguous Cells

    Hi karedog,

    Thank you for your reply. So far all the codes have been very helpful. One last thing is to make it flexible instead of fixing it to 3 rows.
    Can it be relative to how many rows I select and perform the same task as the above code? Thanks.
    Reply With QuoteRegister To Reply
  6. 11-25-2016, 06:14 AM #6
    Doc.AElstein
    Doc.AElstein is offline
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Select & Copy Non-Contiguous Cells

    Hi Ichigo,
    I am also not too clear on exactly what you want.

    But it may be worth pointing out that you can only copy a Range Object made up of of Non-Contiguous Cells ( that is to say a Range Object made up of two or more Areas of Contiguous Cells), If the Areas of the Range Object are “In Line”

    I mean you could copy these two Areas in a Worksheet ( The resulting Range Object has a Range.Areas(1) and Range.Areas(2) )
    Using Excel 2007 32 bit
    Areas1,Cells(1) Areas1,Cells(2)
    .
    .
    Areas2,Cells(1) Areas2,Cells(2)
    Worksheet: RangeObjects

    And you can copy these three Areas in a worksheet ( The resulting Range Object has a Range.Areas(1) , Range.Areas(2) and Range.Areas(3) )
    Using Excel 2007 32 bit
    rngObj rngObj rngObj rngObj rngObj rngObj
    rngObj rngObj rngObj rngObj rngObj rngObj
    Worksheet: RangeObjects
    _.....

    But you cannot copy these two Areas of cells ( It is never the less a valid Range Object obtained with via like RngObject=Range("A3:A4,D1:E2") or RngObject=Application,Union( Range("A3:A4" ,Range("D1:E2") . The resulting Range Object has a Range.Areas(1) and Range.Areas(2)
    http://powerspreadsheets.com/excel-vba-range-object/     )

    Using Excel 2007 32 bit
    rngObj rngObj
    rngObj rngObj
    rngObj
    rngObj
    Worksheet: RangeObjects

    Alan
    Last edited by Doc.AElstein; 11-27-2016 at 06:11 PM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )
    Reply With QuoteRegister To Reply
  7. 11-27-2016, 07:52 AM #7
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Select & Copy Non-Contiguous Cells

    @Alan :
    A nice explanation

    @Ichigo :
    You are welcome.

    Try this :
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 11-27-2016, 10:02 PM #8
    Ichigo
    Ichigo is offline
    Registered User
    Join Date
    03-06-2009
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    47

    Re: Select & Copy Non-Contiguous Cells

    Hi Karedog & Alan,

    Thank you for your explanations and help. Finally, I managed to get the macro to work.
    Thanks to your codes, I managed to slowly study, try and edited it to be:

    Sub Test()

    Range(Selection.Resize(, 3).Address & "," & Selection.Offset(, 5).Resize(, 2).Address).Copy

    End Sub

    Sorry if my explanations were unclear as to put them into words were not easy compared to face-to-face.
    Thank you once again.
    Reply With QuoteRegister To Reply
  9. 11-27-2016, 10:32 PM #9
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Select & Copy Non-Contiguous Cells

    You are welcome Ichigo, many thanks for the rep.points.
    Don't forget to mark this thread as solved :
    please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Regards
    Reply With QuoteRegister To Reply
  10. 11-28-2016, 05:21 AM #10
    Doc.AElstein
    Doc.AElstein is offline
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Select & Copy Non-Contiguous Cells

    Hi Ichigo
    Thanks for the Rep

    Here how to mark Thread as solved: ( You need to be logged in to do that )
    ThreadSolved.JPG
    http://imgur.com/upfFsAT
    Attachment 491107


    Quote Originally Posted by Ichigo View Post
    as to put them into words were not easy compared to face-to-face.
    I agree. Internet is not so good as Real Life contact, but we have the chance to meet people easilly from very far away
    Alan
    Last edited by Doc.AElstein; 11-28-2016 at 05:23 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to select non-contiguous union of cells based on values in other column
    By gutkinma in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-14-2014, 07:41 AM
  2. [SOLVED] Copy non contiguous cells to new sheet
    By timpie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-03-2014, 12:46 PM
  3. [SOLVED] copy and paste from contiguous cells to non-contiguous cells using the = function
    By shameus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-05-2013, 06:48 PM
  4. Select & copy non-contiguous range
    By SunRise in forum Excel General
    Replies: 0
    Last Post: 01-08-2013, 05:56 PM
  5. Select last contiguous range of cells in a column (seached)
    By IconoclastDX in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-22-2011, 07:59 PM
  6. Copy and Paste an array (contiguous & non contiguous ranges)
    By Xrull in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2010, 09:17 AM
  7. Copy Non Contiguous cells
    By jerryliang2k in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2008, 02:01 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1