+ Reply to Thread
Results 1 to 9 of 9

Delete top row in merged cells while keeping text?

  1. #1
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    30

    Delete top row in merged cells while keeping text?

    Hello,

    I have a problem that I hope you guys can help me with.

    I will for simplicity make the problem a bit basic and then I hope I by myself can transform into the bigger picture :-)

    I have two columns. Column A is the "maintask" and column B is subtasks. Usually a maintask have between 1-5 subtasks.
    I have made the sheet so that maintasks in column A merges so it fits with the number of subtask, i.e. if I have 3 sub tasks in B1:B3 then the maintask title is merged to fit cells A1:A3.

    This is all very nice, and I have no problem deleting subtasks IF they arent in the top cell, i.e. B1. The macro is so that it deletes the entire row, so when deleting B1's subtask, then entire maintask is also deleted since the title of merged cells is stored in top cell, i.e. A1.
    How can I delete subtasks in the top cell without it deleting the title in the merged cells that contains the maintask title?

    Please remember that I have simplified the problem :-)

    Hope you guys can help!

    Br
    Fred

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: Delete top row in merged cells while keeping text?

    Get rid of the merged cells; nothing but trouble.

    Use Conditional Formatting to "hide" (format text as same colour as background) for duplicate entries to have a similar effect.

    This would allow you to delete whatever rows you want.

    Post a sample workbook if this doesn't give you enough to go on.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Delete top row in merged cells while keeping text?

    This will however make the layout a lot worse?

  4. #4
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Delete top row in merged cells while keeping text?

    My point is:

    Won't this just make the title of the "box" stand in an odd position compared to a merged cell?

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: Delete top row in merged cells while keeping text?

    There's little point in having a hypothetical discussion about what your spreadsheet looks like now and what it might look like without merged cells.

    I suspect you have to make a manual intervention given that the number of rows can vary. My suggestion would eliminate that effort.

    Post a sample workbook and I'll try to show you what I mean. Or hold my hands up and leave you to persevere with your problem spreadsheet ;-)

    Regards

  6. #6
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Delete top row in merged cells while keeping text?

    I have attached a draft of how my problem is. I have multiple merged cells like the one in column A, but I think you get the picture
    Attached Files Attached Files

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: Delete top row in merged cells while keeping text?

    See the attachment; I have modified it as previously described. Personally, I prefer the name to be visible in the top cell of a list of related cells. I think that's easier than trying to work out where one group ends and the next one starts.

    See if this works for you. Recommendation: don't use merged cells *ever*

    Regards
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Delete top row in merged cells while keeping text?

    I see your point.

    But the problem is that I have already made some conditional format. on the cells depending on the status, e.g. name and so. So it makes it kind of a mess :-S Will try to use your idea though since it seems like the most simple. But then I have to re-do alot of work :-(

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: Delete top row in merged cells while keeping text?

    Sorry.

    Merged cells seem like a great idea but, in my experience, they just serve to complicate and confuse.

    When you are trying to set up a formula or Conditional Formatting ... or insert/delete rows ... they just make everything more difficult than it needs to be.

    I think that "re-doing a lot of work" wil be worth the effort in the long run.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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