+ Reply to Thread
Results 1 to 7 of 7

Using logic function (or other) to determine if cell is merged

  1. #1
    Registered User
    Join Date
    09-05-2010
    Location
    Baltimore, USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Using logic function (or other) to determine if cell is merged

    So let's say I have A1, B1, and C1 on my sheet1, and I am referencing those cells in sheet2.

    In some cases A1 and B1 are merged, and in others they are not. Is there any way on sheet2 to program it, not just to reference sheet1!A1, but also to keep the format of A1, including if that means merging with B1 on sheet2?

    I hope the question makes sense.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Using logic function (or other) to determine if cell is merged

    Think you can use this simple UDF

    Please Login or Register  to view this content.
    then
    =ISMERGED(A1)
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Using logic function (or other) to determine if cell is merged

    Hi and welcome to the board

    Another sensible solution is to get rid of merged cells altogether. They are more than a nuisance in XL as you have experienced

  4. #4
    Registered User
    Join Date
    09-05-2010
    Location
    Baltimore, USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Using logic function (or other) to determine if cell is merged

    Thanks contaminated, the UDF works well, but there are two problems that I have, one more apparent than the other.

    The main issue is that while getting a boolean response is helpful, what I would need to do to follow up with that is to then make the cell that I am running the Function in merge with the adjacent cell if the output is true.

    Is there a command line that can be created in the UDF that would enable this? Like a range.merge?

    While I would love to get rid of the merged cells, it's just not feasible with this sheet that I have inherited to work with.
    Last edited by nyiballs; 09-05-2010 at 03:46 PM.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676

    Re: Using logic function (or other) to determine if cell is merged

    make the cell that I am running the Function in merge with the adjacent cell if the output is true.
    With some arcane exceptions, a UDF can't do anything other than return a value to the calling cell, and merging cells is not one of the exceptions.
    Last edited by shg; 09-05-2010 at 07:35 PM.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    09-05-2010
    Location
    Baltimore, USA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Using logic function (or other) to determine if cell is merged

    My fear is that I am SOL... eh?

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,543

    Re: Using logic function (or other) to determine if cell is merged

    Quoting Mr Spreadsheet about Merged Cells

    "If you attempt to assign a value to a cell in a merged range that's not the upper-left cell, VBA ignores the instruction and doesn't generate an error."

    Perhaps this is what shg means with the arcane.

+ 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