Hi All-
I toiled with getting a macro together to handle an issue I have with line breaks occurring in the middle of a "comments" field in the several csv files I am emailed every week. Often when the data is exported by various users, these breaks cause the fields to be misaligned.
The code I have works perfectly if I have my cursor on col A of an affected row (in each variation of the problem), but I am having a hard time getting it to loop through line by line. When I run the full macro as is, it winds up deleting much more than intended.
Here are the facts:
The break always occurs in column P, resulting in data that should be in columns Q-AQ winds up in the next row, column A or half of the comment winds up in column P below. Because the comments are always split in half, I wrote code to concatenate the data before correcting it with the following steps:
1.First it needs to delete all completely blank rows
2.There are only 9 correct entries for column A. As long as the row begins with one of these, it should be left alone. ("HQ (Crew)", "EVANN", "EWRTH", "ERYE", "EEAST", "CWBIB", "CWPER", "CXPER", "CXWJL")
3.Then apply the fix I have worked out when a line break occurs (shifting, concatenating, moving 2-3 rows of data into one)
4.Finally I want it to return a message box letting the user know if any cleaning was necessary (which I accomplish by comparing the number of rows at the beginning of the macro and at the end.
Attached is a shortened sample version of the a problem file. Below is the code I am using.
I have it pretty thoroughly commented regarding what needs to happen when and where the issues are occuring.
I really would sincerely appreciate any guidance you might have.
Thanks,
J
Please Login or Register to view this content.
Bookmarks