+ Reply to Thread
Results 1 to 12 of 12

minor tweek to merge cell macro needed

  1. #1
    Registered User
    Join Date
    05-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    10

    minor tweek to merge cell macro needed

    Hi guys,

    Hoping you can help. I found the below macro to merge cells on this forum and I need it slightly editing if anyone would care to help!

    Essentially this examines values in one column, and merges if the value below is identical. I need the code to examine multiple columns, so if A1=A2 AND B1=B2 then we should merge A1/A2 and B1/B2.

    Thanks in advance for help!

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    07-07-2005
    Location
    England
    MS-Off Ver
    2003 Pro
    Posts
    452

    Re: minor tweek to merge cell macro needed

    That code worked fine for me. I had the following values

    A1 = 1
    A2 = 1
    B1 = 2
    B2 = 2

    I then hilighted those cells before running the macro (because your code is set to loop through the 'selected' cells.

    Do you get any error?

    John

  3. #3
    Registered User
    Join Date
    05-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: minor tweek to merge cell macro needed

    Thanks for the reply John.

    It's not that I get an error - let me explain a little better. For example with the following values:

    A1 = 1
    A2 = 1
    A3 = 2
    A4 = 2
    C1 = 3
    C2 = 3
    C3 = 3
    C4 = 3

    The code will merge C1 through C4. Whereas I would like it to merge A1/2 on the same row as C1 / C2 and then A3 / A4 on the same row as C3 / C4. Does that make sense?

  4. #4
    Forum Contributor
    Join Date
    07-07-2005
    Location
    England
    MS-Off Ver
    2003 Pro
    Posts
    452

    Re: minor tweek to merge cell macro needed

    Its probably possible to do that in your example but what would you want to happen if the cells were like this...

    A1 = 1
    A2 = 1
    A3 = 1
    A4 = 2
    C1 = 3
    C2 = 3
    C3 = 3
    C4 = 3

    John

  5. #5
    Registered User
    Join Date
    05-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: minor tweek to merge cell macro needed

    In the above example we would merge A1 - A3 and then C1 - C4. We would leave A4 as a single cell.

    Does that help to clarify?

  6. #6
    Registered User
    Join Date
    05-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: minor tweek to merge cell macro needed

    Attaching an example with raw data / macro result / desired result tabs.

    Hope someone can help!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-07-2005
    Location
    England
    MS-Off Ver
    2003 Pro
    Posts
    452

    Re: minor tweek to merge cell macro needed

    so the first column determines how the other columns are split?

    e.g column 1 has ABC listed 3 times in a row followed by DEF listed 3 times in a row

    and column 2 has XYZ listed 6 times in a row

    you would want XYZ split into 2 merged cells (with 3 cells in each merge) ??

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: minor tweek to merge cell macro needed

    You really should forget merging cells, MergedCells will eventually cause problems
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  9. #9
    Registered User
    Join Date
    05-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: minor tweek to merge cell macro needed

    Thanks for the replies.

    John - yep that is exactly how it works, the first column is an ID.

    Roy - happy to go down an alternative route if you can advise, I realise many people try conditional formatting, but I wouldn't know how to code for it.

    Thanks

    Sam

  10. #10
    Forum Contributor
    Join Date
    07-07-2005
    Location
    England
    MS-Off Ver
    2003 Pro
    Posts
    452

    Re: minor tweek to merge cell macro needed

    just picking up on Roys suggestion and your conditional formatting comment - i would agree with Roy about merge cells getting messy.

    if you were to colour the cells based on your criteria - how would you want the colouring to work? using my last post as an example, would you want ABC to be the same colour as the first 3 cells of XYZ?

  11. #11
    Registered User
    Join Date
    05-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: minor tweek to merge cell macro needed

    Ideally the conditional formatting would have to recreate the effect of merging the cells.

    I think I have seen some examples where this is possible, setting text to white etc

    But not sure how I would code it, happy to go down this route if someone can assist.

    - Just to add, the data that is merged is only used for presentation purposes, it comes from a system download, therefore it doesn't really matter if it can't be editied after the fact, as long as we could change the system data, and then re-run the macro.

    Additionally it feels like the macro in the above attached Excel is 95% there...
    Last edited by 12588sam; 05-10-2011 at 01:10 PM.

  12. #12
    Registered User
    Join Date
    05-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: minor tweek to merge cell macro needed

    Anyone...?

+ 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