vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows

  • With the two macros near identical below, at times way to often, I'm losing the 'end of row' line breaks when it pastes row data back, after in affect 'cutting' nonadjacent rows to the clipboard. I say 'cutting' because the macro copies the selected nonadjacent rows to the clipboard, then deletes those rows.


    The first macro instantly pastes rows from the clipboard to a new workbook and the other I choose where to manually paste the contents. Depending on whether the source file (where nonadjacent rows are highlighted) was previously saved and what file type...


    Often, upon pasting (instant or manual) ALL THE DATA GOES INTO ROW 1! NOT cool, especially with more than 16 rows with 16 columns because it goes over the 255 column limit and data outside that range is gone. There's no undo for macros of course.


    Here's a perfect duplicatable example and it's as odd to me as any other scenarios where it works perfectly pasting into separate rows or all rows go into row 1!


    Ok from the source file, normally a .csv and always works 100%... I move any number of rows to a new workbook. From a brand new workbook it NEVER works, nor does it even if I SAVE this new workbook as (.csv, .xls or .xlsx), BUT IF I CLOSE AND REOPEN EACH FILE TYPE... .csv and .xlsx work fine! but the .xls version STILL PASTES ALL DATA TO ROW 1. Wow.


    There might be other options how to do the macro, but there HAS to be a way to preserve the 'end of row' line breaks.



    This macro 'Cuts' multiple non-adjacent rows and pastes to a new workbook automatically.





    This one 'Cuts' multiple non-adjacent rows to clipboard for manual paste.




    I got a line on a possible direction, but just have no idea how to incorporate it.


    Obviously it's an inconsistency issue with 'end of row' line break '\r\n' code. Viewing different source files, during testing, there is at
    times only a cr or lf not both. Ironically Windows requires '\r\n' always and all aspects are Windows! I'm not on a linux box or getting files from another platform.



    Is it possible to use a "Replace" to get rid of CR and/or / and or LF and then put them back in again.


    abc(cr) becomes abc and then abc(cr/lf),
    and abc(lf) becomes abc and then abc(cr/lf)
    and abc(cr/lf) becomes abc and then abc(cr/lf)?


    I looked at some codes (google ')VBA cr/lf' and this seems easy and possible.



    I researched this and still feel like a lost ball in tall weeds!


    Thank you for even considering helping with this challenge!

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Not tested this, but looking at your code, it seems that it only adds the line feed character if it hits item 16384 in the selection.


    What happens if you simply delete the if from around the

    Code
    1. str = str & Chr(13)
    2. count=0

    commands?

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Im not sure I am following the issue. Is the issue that you are trying to copy some data and have it paste to the next available row but currently it always pastes to row 1?

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    This will place the selected cell values onto the first sheet of a new workbook in Column A on sequential rows and remove any New Line or Carriage Return characters, is this what you want?


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Quote from Infomage;782870

    Not tested this, but looking at your code, it seems that it only adds the line feed character if it hits item 16384 in the selection.


    What happens if you simply delete the if from around the

    Code
    1. str = str & Chr(13)
    2. count=0

    commands?


    Well ironically both macros work the same without that code! lol


    But it doesn't affect the 'end of row' line break issue. :(

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Quote from bryce;782884

    Im not sure I am following the issue. Is the issue that you are trying to copy some data and have it paste to the next available row but currently it always pastes to row 1?


    This macro CopytoClipboardDeleteNewandPaste takes any number of highlighted adjacent and/or nonadjacent rows in a workbook and 'cuts' and pastes them into a new workbook it opens.


    The other macro CopytoClipboardDeleteNewandPaste2 takes any number of highlighted adjacent and/or nonadjacent rows in a workbook and holds them on the clipboard for a manual paste.


    Both have a problem with 'end of row' line breaks when the source file (where the rows are highlighted) is a new unsaved file or EVEN a saved file (.csv, .xlsx) UNLESS it's closed and reopened and saved .xls files, even closed and reopened, do not maintain row line breaks.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Whatever designates the end of row 1, to know this is now row 2, and row 3 and so on.


    I'm not sure how it works, but I assume there's some line break Cr, Lf or CrLf to designate one row from another or copying even adjacent rows (say row 5 through row 10) normally would then paste them into another document say word ALL on ONE line rather than individual rows.


    As was mentioned to me this has something to do adding vbCrLf and removing single Cr and Lf if present.


    When I viewed saved files in a text editor it's odd. .csv files always show CRLF at the end of each row. .xls shows mostly CR and .xlsx mostly LF, some CR, but never both.


    Because of this when the source workbook that I'm 'cutting' rows from doesn't have CRLF present at the end of the row, of course it's going to turn around and paste all the rows into row 1!


    I need to fix this, so that it doesn't matter what the source file uses, like an unsaved workbook that I assume uses NO Cr, Lf or CrLf yet so if my macro doesn't add vbCrLf at the end of each row it will never work right.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Quote from KjBox;782897

    This will place the selected cell values onto the first sheet of a new workbook in Column A on sequential rows and remove any New Line or Carriage Return characters, is this what you want?



    This cuts the first row of my selected rows and slows way down and eventually errors on the last line of your code. :(

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    You really need to explain more about what you are trying to do, which cells are you selecting, all in one row, all in one column or a mixture?


    Why not post a sample workbook and expected result to show just what you need to do.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Keep in mind both macros work with any workbook and any data. I even tried it with a basic letter with each line of text on it's own row.


    fyi I'm always selecting the entire row of course.


    Consider a workbook with 10 rows of data. In this example I'll use that there's 17 columns to column Q, because it's common for our file but makes no difference.


    Then select (highlight) row 1, 3, 5-8. Now run the macro. That's it.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Try this



    Of course assumption is made that your CSV or Excel file structure is correct, data starts in column A and there are no empty columns within the data.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    omg just to see it preserve the columns was amazing!


    There's only one problem, and it's totally my fault. Some cells have multiple words. (i.e. street address)


    Your macro only prints the first word in the new workbook, in this case it's the street number (i.e. 3435) The rest, say (Brockworth Drive Suite 233) doesn't get moved.


    To prepare, I also tried editing your macro to replace the other macro, but no luck so far. I need an option to keep it on the clipboard to paste the rows manually somewhere but couldn't get it to paste properly. It only pastes some unreadable characters.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Ahhhh, now I see what you meant by "CR, LF or CRLF" at end of row, you were referring to end of line in a multiline cell?


    OK this requires a few macros.


    Two procedures in a standard module:



    One procedure in the Worksheet Object Module for "Sheet1"



    The first procedure in the standard module is the one that you need to run after selecting the rows. You will get a message asking if you want to paste to a new workbook immediately or later.


    If you click "No" ( which means you want to paste later) then at any time you can activate the new workbook and select cell A! on the first sheet, then open the clipboard ("Home" tab on the ribbon, click the small arrow bottom left of the "Clipboard" area).


    You should see the string copied to the clipboard by the macro you ran from the Excel or CSV file somewhere near the top of the list in the clipboard window (the exact position will depend on what else has been added to the clipboard since the macro you ran from the Excel or CSV file). The string will begin with something like "$A$1| xxx" (if row 1 was the first row you selected, xxx will be the contents of the first cell of the first row selected).


    Click that string, the sheet will populate, then you can close the clipboard window.


    I have included code to save the new workbook and indicated in the code where you can change the name and Directory/Folder saving location for the new file.


    If you don't want to save the file automatically comment out this line in the standard module (it is line 57)

    Code
    1. .SaveAs sFilePathName, 51


    and this line in the Worksheet Object Module (it is line 20)

    Code
    1. ThisWorkbook.SaveAs sFilePathName, 51

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Note: I missed commenting out saving the file. I'll test more. On a side note also a little worried how an object module in sheet one will affect my plethora of macros and modules. I'm running it outside my main workbook.xlsb template for now.



    This is very cool! :wowee:


    I just need to keep it more basic and flexible, for several reasons.


    The biggest thing is, with tons of macros and open files flying around, I can't have it save to a specific filename. In both of our macros whether I keep the cut rows on the clipboard or move them to a new workbook instantly, nothing saves the file. Other macros run for that plus other stuff like creating multiple file types etc.


    Of course if I leave your file open and run the macro again of course it can't write to an open file but, again, that's only part of the issue. I move around through several files and need to grab rows from one file, move to another and maybe grab a few rows from that file back to another and so on. I need to keep it light and work on the fly.


    A good test, and a major hangup of our macros is start from a source file. Highlight a bunch of adjacent and nonadjacent rows, move (cut/paste) to a new workbook and repeat the process again from there, highlight some rows and run it again to another new workbook. 100% of the time here I loose all line breaks. When I do this with your macro, after having to close your file FileWithCopiedRows.xlsx which defeats the purpose :( Then run it again with some of the rows from an unsaved workbook, like in my example, this line errors .Columns(1).SpecialCells(4).EntireRow.Delete. It can't SEE any data, even if highlighted.


    I'm SO sorry! You put your time and effort into this KJBox! :(

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Quote

    Note: I missed commenting out saving the file. I'll test more. On a side note also a little worried how an object module in sheet one will affect my plethora of macros and modules. I'm running it outside my main workbook.xlsb template for now.


    Yes try it without saving.


    You will note that I included an error trap in the Worksheet_Change event code that is copied to the new Workbook. If the workbook is not named like "Book" the macro will exit, that means it will only run on the newly created workbook to which it was copied.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Crud. I always feel like a lost ball rolling around tall weeds!


    Funny, right away I got it to save the filename I'm I'm pretty sure the file was not called book anything, like a new workbook.


    I thought I'd be cute and create a brand new .xlsm file with only the object in sheet 1 and one module for the macro


    Now I get a compile error instantly - user-defined type not defined at obj As New MSForms.DataObject


    When I use my main .xlsb template with or without commenting out saving it won't see the cells anymore rt 1004 no cells found errors at .Columns(1).SpecialCells(4).EntireRow.Delete


    I swore it worked a few times! No it did. :(

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    If you have other Worksheet_Change event code in your .xlsb file then you could insert a new module into that files VB and name it "modCodeToCopy".


    Put the Worksheet_Change event code into the new module


    Change the "ModuleToNewWorkbook" procedure to

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Here is the file I used for development and testing, I have moved the Worksheet_Change event code to its own standard module as explained in my last post. Also commented out the file saving.


    Try it using this file.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Make sure you have the "Microsoft Forms 2.0 Object Library" reference checked in the References window (Tools>References in the VB Editor)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.