vba macro: I'm losing (row) line breaks cut/pasting multiple (non-adjacent) rows into a different workbook

[wittonlin]

...far too often. I'm using Excel 2007 on Win8.1, which is part of the problem with max 255 columns, but I really need to address the problem not the symptom anymore.

The process is really straight forward. The range of outcomes is ridiculous though! The 2 macros below, LIFE savers, are used so I can highlight multiple non-adjacent rows at once (i.e. row 2 through 15 to column P, then row 30 through 45 to column P, and so on.) AND 'in affect' cut those rows to the Windows clipboard for pasting into a new workbook or with the other macro automatically paste the 'cut' rows into a new workbook. (i.e. row 1 through 28 to column P)

...And instead ALL the data pastes INTO ROW 1, FAR too often! What really sucks is that when it happens with over 15-16 rows of data the full range ends up outside the 255 max columns rang and is gone for good!

Obviously it's a CR/LF issue. But I've viewed all file types many many times in a text editor displaying all characters and it's all over the place! Both versions of excel are completely random! And instead of \r\n I see only random single character at the end of each line, either \r or \n.

CSV files always end each line with the proper CRLF as it should, but STILL at times losses ALL line breaks and 100% of the time when rows are copied/cut into a new blank workbook and not saved and closed, then cut from that workbook into another.

This lack of any rhyme or rhythm to why this happens so sporadically is really frustrating. ALL unsaved files, MOST .xlsx files then it's about 50/50 on .xls files this occurs. CSV files are by far the best, but not only does the file have to be saved, but it has to be close and reopened before the row (line breaks) are maintained! Even then at times with csv files, no line breaks. All data pastes into row 1.

It's been such a problem I had to get help to make another macro that would fix the data, bringing the range of rows, now ALL on row 1 back to the rows where it goes.

It's been 6 months and it's driving me nutzo! There HAS to be some parameters that can be added to the following macros so this stops happening...I'm REALLY hoping.

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

Sub CopytoClipboardDeleteNewandPaste()

Dim obj As New MSForms.DataObject
Dim x, str As String
Dim count As Integer

count = 0

For Each x In Selection

count = count + 1
    
If x <> "" Then
If count = 1 Then

    str = str & x
    Else
    str = str & Chr(9) & x

End If
End If

 If count = 16384 Then
        str = str & Chr(13)
    count = 0
    End If

Next

obj.settext str
obj.PutInClipboard

Selection.Delete Shift:=xlUp

Workbooks.Add
ActiveSheet.Paste

End Sub

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

Sub CopytoClipboardandDelete2()

Call SaveWorkbook

Dim obj As New MSForms.DataObject
Dim x, str As String
Dim count As Integer

count = 0

For Each x In Selection

count = count + 1
    
If x <> "" Then
If count = 1 Then

    str = str & x
    Else
    str = str & Chr(9) & x

End If
End If

 If count = 16384 Then
        str = str & Chr(13)
    count = 0
    End If

Next

obj.settext str
obj.PutInClipboard

Selection.Delete Shift:=xlUp

    Sheets.Add after:=Sheets(Sheets.count)
    ActiveSheet.Paste
    Selection.Cut
    ActiveWindow.SelectedSheets.Visible = False

End Sub

Thank you for any possible guidance!
Mark

 

[mole999]

2003 was limited to 255, 2007 takes you to 16 thousand, save the file as xlsm or xlsb

 

[wittonlin]

2003 was limited to 255, 2007 takes you to 16 thousand, save the file as xlsm or xlsb

Thanks Mole! Interesting thought. I'll test editing some of my macros to see how this would work.

The trouble is that this, again like my macro to fix all the rows going to row 1, addresses the symptoms not the problem. Kinda like modern medicine in the West.

I really need to find code that will preserve the line breaks (rows) or add it if missing, so the data always pastes into consecutive rows. There just HAS to be a way!

 

[wittonlin]

Here's some clarification.

With the two macros a few posts up, AT TIMES, 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 senerios 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.

 

[wittonlin]

Here's a line on a possible solution from someone, but I 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!