Results 1 to 19 of 19

Thread: Copy/Paste with blank cells inbetween

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Copy/Paste with blank cells inbetween

    I have a worksheet where B3 is "1", B4 is "5", B5 is "7" etcetera to B8. I'd like to paste those values to another worksheet starting in C5 but with a blank cell in-between the output cells. In other words D5 would be "1", D6 would be blank, D7 would be "5", etcetera, and I'd like to do it without a link and instead perhaps use Transpose or Join.

    BTW WHAT HAS HAPPENED TO THE FORMATTING TAGS FOR THESE POSTS?

  2. #2
    Addicted Member
    Join Date
    Jul 2022
    Posts
    242

    Re: Copy/Paste with blank cells inbetween

    You could use a for loop, it would look something like:

    Code:
        Dim fromRow as Integer
        Dim toRow as Integer
    
        Dim fromWS as Worksheet
        Dim toWS as Worksheet
    
        Set fromWS = ThisWorkbook.Sheets("Sheet1")
        Set toWS = ThisWorkbook.Sheets("Sheet2")
    
        toRow = 5
        For fromRow = 3 to 8
            tows. Cells(toRow, "D").Value = fromWS.Cells(fromRow, "B").Value
            toRow = toRow + 2
        Next fromRow
    
        Set fromWS = Nothing
        Set toWS = Nothing

  3. #3

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    If you mean with a Formula, the only thing coming close to your requirement was this:
    https://www.reddit.com/r/excel/comme...ank/?rdt=54620

    Note: This is for blank Columns.
    Will have to be reworked for Rows (if it's even possible)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: Copy/Paste with blank cells inbetween

    Zvoni, I am looking to insert blank columns, and the formula in your reference looks promising, but despite what my question said, the number of values in the source is variable and there could be a hundred or more values in it, so I assume I would need to use VBA to populate the formula across the destination row. Am I correct?

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    You're right: In your first post there is no mention of blank columns to insert, only "Transpose" hinting at it.

    That said: I'd need to see a "This is what i have, and this is what it should look like"
    because, frankly: I'm struggling imagining the end result
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    Sorry, Martin.

    Company-Laptop here. No Access to filesharing services, nevermind xlsm-file

    Would prefer a Screenshot (if the current Forum-Issues allow that)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: Copy/Paste with blank cells inbetween

    The forum is not even allowing me to add a picture so this is the best I can do.
    SOURCE
    Column A: a date
    Column B: 1
    Column C: 2
    Column D: 3
    Column E: 4
    Column F: 5
    Column G: 6
    Column H: 7

    DESIRED OUTPUT
    Column D: 1
    Column E: blank
    Column F: 2
    Column G: blank
    Column H 3
    Column I: blank
    Column J: 4
    Column K: blank
    Column L: 5
    Column M: blank
    Column N: 6
    Column O: blank
    Column P: 7

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    hmm.... i take it, the source is all in one row across the mentioned columns, and the Destination is on a different worksheet/some rows below it?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    I think i have it

    I took your description above, and filled an Excel sheet with your sample Data.
    Starting in Column B1, and continuing through C1, D1 etc.... i entere: 1,2,3,4,5,6,7 (all in Row 1)

    I then entered following Formula in D4:
    =IF(MOD(COLUMN(B1),2)=1,"",OFFSET($B$1,,ROUNDUP(COLUMN(B1)/2,0)-1))

    and "copy-dragged" it to the right
    Value in D4 is 1, in E4 is blank, in F4 is 2, G4 is blank...etc.

    Important: Look closely at relative/absolute Addressing in the Formula
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    OK, tested it in a different sheet, too.
    Just add the Sheet-Qualifier to the Addresses ("Sheet1!B1" etc.)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    uh.... very nice feature of the Forum now: If you go on Edit (to edit your post), your "original" Entry vanishes......
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: Copy/Paste with blank cells inbetween

    BTW, I know how to use Join on the source data to produce a string like 1-2-3-4-5-6-7 where the dashes represent spaces, but I could not figure out how to paste that string into the output row formatted like
    Column D: 1
    Column E: blank
    Column F: 2
    Column G: blank
    Column H 3
    Column I: blank
    Column J: 4
    Column K: blank
    Column L: 5
    Column M: blank
    Column N: 6
    Column O: blank
    Column P: 7
    in just one statement.

  16. #16
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    See my Description/Formula in Post 12
    Tested in the same sheet as well as in a different sheet (albeit with Sheet-Qualifier in the Cell-Address)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  17. #17

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: Copy/Paste with blank cells inbetween

    Thanks, but as i mentioned above "...the number of values in the source is variable and there could be a hundred or more values in it, so I assume I would need to use VBA to populate the formula across the destination row. Am I correct?"

  18. #18
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Copy/Paste with blank cells inbetween

    In that case i?d say yes, since i?m not aware Excel being capable to determine last column/row during runtim to adjust/propagate formulas forward
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  19. #19

    Re: Copy/Paste with blank cells inbetween

    Have you considered using a dynamic VBA solution to populate the formula across the destination row, given the variable number of values in the source data?

Posting Permissions

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



Click Here to Expand Forum to Full Width