Copy non continuous data to a continuous range -VBA
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Copy non continuous data to a continuous range -VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Copy non continuous data to a continuous range -VBA

    I want to set the value for G36:G54 to data from cells

    A22
    A25
    A28
    A31
    A34
    A37
    A40
    A43
    A46
    A49

    I cannot seem to get the coolest way to get it done.

    Can someone pull it out for me?
    There Is Always A Better Way

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,680
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    The Range looks larger then the Ranges shown.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    Oh I just saw that

    My keyboard auto correct that

    Its G36:G45

    10 cells each
    There Is Always A Better Way

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    Quote Originally Posted by kelly mort View Post
    I want to set the value for G36:G54 to data from cells

    A22
    A25
    A28
    A31
    A34
    A37
    A40
    A43
    A46
    A49

    I cannot seem to get the coolest way to get it done.

    Can someone pull it out for me?


    Maybe this?

    Code:
    Sub Test()
        iStart = 22
        For Each xlCell In Sheets("Sheet1").Range("G36:G45")
            xlCell.Value = Sheets("Sheet1").Range("A" & iStart).Value
            iStart = iStart + 3
        Next xlCell
    End Sub

  5. #5
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,237
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    Code:
    Range("G36:G45").Value = Range("A22:A49").Value
    or this :
    Code:
    Range("G36:G45").Formula = Range("A22:A49").Formula
    Last edited by Jaafar Tribak; Jul 22nd, 2019 at 10:34 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,680
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    Try this:
    Code:
    Sub Fill_Range()
    'Modified 7/22/2019 10:33:17 AM  EDT
    Dim i As Long
    Dim x As Long
    x = 22
    For i = 36 To 45
        Cells(i, "G").Value = Cells(x, 1).Value
        x = x + 3
    Next
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,237
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    Sorry, I didn't pay attention to "non continuous" !
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  8. #8
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    The codes for both @Steve and @My Answer Is This

    Worked fine.

    I appreciate that.
    @Jaafar Tribak
    Yours didn't do the job
    There Is Always A Better Way

  9. #9
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    Quote Originally Posted by kelly mort View Post
    The codes for both @Steve and @My Answer Is This

    Worked fine.

    I appreciate that.
    Happy to help!

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,680
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Copy non continuous data to a continuous range -VBA

    Glad I was able to help you.
    Come back here to Mr. Excel next time you need additional assistance.
    Quote Originally Posted by Steve_ View Post
    Happy to help!
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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