+ Reply to Thread
Results 1 to 43 of 43

Need to delete multiple lines in cells but keep one

  1. #1
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Post Need to delete multiple lines in cells but keep one

    Need a Macro that does the following:

    For cells with multiple lines in a range
    1. Deletes all dates/text that are striked out
    2. Keeps only the remaining date/text that is next-in-line whether it be black and not striked out, colored, or italicized
    3. Deletes the other dates/text following that kept date/text
    4. Changes the color of the kept date/text to black (if it was red or a different color)
    5. Does not change the dates/text font format whether it be italicized or not
    6. Does not change the cells fill color

    Examples: the following are lists in individual cells. ALSO SEE ATTACHMENT for sample file
    ---------------------------
    08/01/12 *underlines indicate strikethrough
    06/01/13
    12/31/12
    05/31/13
    06/30/13
    06/30/13
    -----------------------------
    06/30/12
    01/31/13
    03/07/13
    06/05/13
    -----------------------------
    06/08/11
    12/09/11
    -----------------------------
    09/22/11
    10/31/12
    03/06/12
    -----------------------------
    09/01/10
    01/01/12
    N/A
    Design-Build
    ---------------------------------

    After running the macro, the result should look like this for each individual cell based on the previous examples
    -----------------------------
    06/01/13
    ----------------------------
    06/05/13
    ---------------------------
    06/08/11
    ---------------------------
    03/06/12
    ---------------------------
    N/A
    ---------------------------

    Any helpful comments or suggestions are welcome. Thank you for the help in advance.
    Javier20h
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    desired result on attachment would be helpful
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    I have added the desired results into the file. Thank you for the suggestion.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Thank you for your reply. I tried running the code but I get an error message at the line "MyText = MyText & .Text" saying, < unable to get the text property of character class >. Also, does this macro run for the entire sheet or a selected range? Thanks again for the help

  6. #6
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    You don't need select anything.
    I tested the code on your sample file, attach please your file with my macro

  7. #7
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    I got it to work on that file. I was testing it on another file that I will post. This is the file im working on excluding unneeded info. I found that the problem is the "project no" column. If there is a number in there, the macro won't run. after I deleted the project numbers, it ran.

    Also, I noticed that the cells that have a strikethrough date(s) followed by a red date, the red dates are being deleted but they are not supposed to be. The red date need to be kept and changed to black if possible.

    And I'm not sure if its possible but is there a way to shift the values up (by delete the empty lines above) to the first line in the cell after deleting the dates before.

    Thank you for the help. Its almost perfect.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    It looks like some strikethrough dates are kept as well after running the macro.

  9. #9
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    the range of change is always the same? starting from column O to S ?
    If yes you can change this line
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Last edited by patel45; 10-16-2013 at 02:43 AM.

  10. #10
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Yes, the range should always be the same. Now I just need to fix the following minor problems:
    1. The cells that have a strikethrough date(s) followed by a red date, the red dates are being deleted but they are not supposed to be. The red date need to be kept (and changed to black if possible).
    2. Is there a way to shift the values up (by delete the empty lines above) to the first line in the cell after deleting the dates before?
    3. Some strikethrough dates are kept after running the macro.

    Thanks

  11. #11
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    BTW, the insert of "Set Rng = ActiveSheet.Range("O7:S" & numrow)" enabled the macro to run without error.
    Last edited by Javier20h; 10-16-2013 at 04:43 PM.

  12. #12
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Amazing, it works almost exactly right. There is only one problem.

    1. The red dates and italic dates after strikethrough dates are being deleted. See rows 21,22,23,25,51 column o (format?),61,62,64,and others -- AFTER RUNNING THE MACRO

    I posted the updated file with the improved macro. I included in the file the desired results and the original info.

    The last thing to fix is to keep the red and italic dates that follow strikthough dates or that are the first line in the cell.

    Thanks for all the help. Almost Solved!
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    I'm sorry but I'm confused, Now you know how the code works, you can arrange it for match your goal

  15. #15
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    This is what I was trying to say: If there is a strikethrough date followed by an italic or/and a red colored date, that date is deleted and the cell is blank. There should be a date that remains. If you run the code, you will see blanks where this situation occurs. There should be at least one line of information in every cell. Compare the desired to the tested after running the macro (in the most resent uploaded file). I will try to rearrange the code to fit my goal but if you or anyone can figure it out before I do that would be very helpful.

    Thank you again.

  16. #16
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    attach a small samplefile with only not solved cases

  17. #17
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Good suggestion! I will post a file shortly

  18. #18
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Hope this shows the problem... CAUTION... there may be a cell format problem.
    Attached Files Attached Files
    Last edited by Javier20h; 10-18-2013 at 02:57 PM.

  19. #19
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Nice! That fixed the Red font problem. One last problem:
    1. Italic dates are not being kept. (Again, this may be formatting problem)

    Im posting another sample file that shows this problem. After this, then all my problems will be solved.
    Thanks for the continuing help.
    Attached Files Attached Files

  21. #21
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    try removing Or .Font.Italic from
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    That almost worked. There are some cases where Italic font should be deleted such as after a date that is saved because it was not striked through. However, if there is an italic date first in the first line of a cell (or after some strikethrough dates), it should be kept and the dates following should be deleted.

    In other words not all italics should be kept or deleted. Should be deleted if it follows a non strikethrough date. Should not be deleted if it is the first date after strikethroughs or first date in the cell. I think there needs to be a condition that evaluates these situations in the code.

  23. #23
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Another idea, If we can delete all font that is in the second line of a cell (not in the first line), after running the bbtest4 code, that should solve the problem. Just an idea

  24. #24
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    I stumbled across this code that creates a pop-up window and allows you to keep the first X amount of characters and select a range. I kept the first 9 characters and that seemed to work well for my problem. There should be a way to change the code to eliminate the pop-up menu and change the code to always keep the first 9 characters for our range. Im sure if there is a way to include this code at the end of the existing one so that it will run one code after another.

    Please Login or Register  to view this content.

  25. #25
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    It works really good. I have two small problems.
    1. is that some of the Italic Dates get changes to regular (because of the format of the lines above, I believe). I havent been able to figure out why they change to non-italic or a way to keep them italic. I need to check the formatting of the cell

    2. all the font colors dont get changed to black in the end

    Here are the current codes I am using to Solve this problem.

    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.

  27. #27
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    did you solve ?

  28. #28
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    almost, I need to merge the codes or change the bbtest4 so that it changes the all the text in the range to black at the end.

  29. #29
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    is there a way to run two codes under one macro? or merge these codes into one.

  30. #30
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    another problem: running the code on an earlier sample file deletes some wording in cells that i want to keep. I'll post a new sample file showing this. My thought for a solution:

    1. Run the code without keeping the first 9 characters
    2. Then, delete everything after the first line in a cell EXCEPT words/lettering/phrases such as "N/A" or "Design-Build"

    There may be a better way to do this. That is just my thought process.

    And the italic problem is still an issue. When the dates get shifted up they change from italic to normal. Also, some dates get mashed together after running the code. Dont know what is doing that either. I'll investigate further.
    Attached Files Attached Files

  31. #31
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    Please Login or Register  to view this content.

  32. #32
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Im getting a run-time error at the line ".ColorIndex = xlAutomatic". Everything seems right. What could be the problem?

    Here is the updated samplefile with bbtest6.
    Attached Files Attached Files

  33. #33
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    fixed it...
    Please Login or Register  to view this content.

  34. #34
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Works great! Really good... one small small problem. One cell in the entire file isnt resolving. Here is the samplefile with problem. The yellow highlighted cells. I thought the code was going to keep the first line which is "N/A" and search to keep "Design-Build", but after running the code, its keeping the first 9 characters which are "N/A" and "Desig" as you will see, then searches for "N/A" and "Design-Build" which doesnt exist after keeping the 9.
    Attached Files Attached Files

  35. #35
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    Please Login or Register  to view this content.

  36. #36
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    That code did't work. It made things worse by copying and replacing with "N/A" and "Design-Build" into the cells that follow.

  37. #37
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    This is the round about way how I fixed this. I kept bbtest6, then I recorded a macro that replaces "Desig" with "Design-Build" but then another problem happens because the other Design-Builds get altered to "Design-Buildn-Build" so then I replace Design-Buildn-Build with "Design-Build". This is a random way to fix this problem. Here is the code:
    Should we add this into the main code or find another solution?

    Please Login or Register  to view this content.

  38. #38
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    Is there a way to change the recorded macro to only effect the same range.

    Also, I need to change the code to exclude any cells with the color RBG (247, 151, 70) from bbtest6. these cells have a long string of letters that i need to save. The current code keeps the first 9 characters which deletes a lot of the content in those cells.

  39. #39
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    or exclude cells that contain the words "Contruction Completed"

  40. #40
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    Please Login or Register  to view this content.

  41. #41
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    I ran the code but for some reason, the cells that contain the words "Construction Completed" still get edited by the 9 characters line. I think it has to do with the order in the code. Probably have to rearrange the code. Something like:
    1. Move "If InStr(cell.Text, "Contruction Completed") = 0 Then" down before "...cell.text, 9"
    2. add a condition such as... if the cell contains "Contruction Completed ...", then skip cell
    3. If the cell doesnt have that then edit cell to 9 characters.

  42. #42
    Registered User
    Join Date
    10-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need to delete multiple lines in cells but keep one

    there was a typo... "Construction Completed". the Code worked great! I think it is solved. thanks for all the help. I may post more simple question in the future. I hope you will help again. Thank you.

  43. #43
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need to delete multiple lines in cells but keep one

    it was a pleasure to help you, you have been proactive, not all are so

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to delete lines where there are successive blank cells
    By Footley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-28-2012, 12:16 PM
  2. [SOLVED] Macro to Delete useless lines, rearrange by dates and skip lines between different days.
    By Tmc2159 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-12-2012, 02:15 PM
  3. Merged Cells and multiple lines
    By ATLGator in forum Excel General
    Replies: 2
    Last Post: 03-29-2012, 10:03 AM
  4. Compare cells to delete lines?
    By lancekeith in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2006, 08:35 PM
  5. [SOLVED] Is there a way to delete multiple lines at the same time beside u.
    By cherry in forum Excel General
    Replies: 2
    Last Post: 04-11-2005, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1