Microsoft Excel Webinar

Results 1 to 7 of 7

Thread: Finding merged cells

  1. #1

    Question Finding merged cells

    Hi there,


    In a table we have 5 rows and 5 columns and in 4th row cell 1 and 2 are merged so the row 4 is now containing 4 cells.

    Now

    VB:
    Selection.Tables(1).Rows(1).Cells.Count 
    
    
    Formatting tags added by mark007
    gives 5
    VB:
    Selection.Tables(1).Rows(2).Cells.Count 
    
    
    Formatting tags added by mark007
    gives 5
    VB:
    Selection.Tables(1).Rows(3).Cells.Count 
    
    
    Formatting tags added by mark007
    gives 5
    VB:
    Selection.Tables(1).Rows(4).Cells.Count 
    
    
    Formatting tags added by mark007
    gives 4
    VB:
    Selection.Tables(1).Rows(5).Cells.Count 
    
    
    Formatting tags added by mark007
    gives 5

    By looking at the table I can say that first two cells are merged in 4 row but using VBA can I locate a merged cell and its length?


    Thanks in advance.

    Chandan

  2. #2
    I am not quite understanding the question. By your examples, the .Count is correct. Row 4 does indeed have a .Count = 4.

    What, exactly do you mean by "locate a merged cell", and what do you mean by "its length"?

    A cell does not retain any property to indicate it has been merged. Once merged, a cell just is what it is. The only way to give a clue that it was merged, is to compare the cells to each other and calculate something.

    But which one? I am attaching a demo. Execute the code by clicking "Locate Merged Cells" on the top tool bar. here is the code.
    VB:
    Sub LocateMergedCells() 
        Dim aTable As Table 
        Dim aCell As Cell 
        Dim CellString As String 
        Set aTable = ActiveDocument.Tables(1) 
        For Each aCell In aTable.Range.Cells 
            If aCell.Width <> aTable.Cell(1, 1).Width Then 
                CellString = aCell.Range.Text 
                If CellString <> Chr(13) Then 
                    If CellString = Chr(13) & Chr(7) Then 
                        aCell.Range.Text = "I seemed to be merged." 
                    Else 
                        aCell.Range.Delete 
                    End If 
                Else 
                    aCell.Range.Text = "I seemed to be merged." 
                End If 
            End If 
        Next 
        Set aTable = Nothing 
    End Sub 
    
    
    Formatting tags added by mark007
    Note that the comparison (by width) is to Cell (1,1). But what if Cell (1,1) is the merged cell????? There is no way of knowing.

    I made the code do a toggle. Click it once, it puts text into what it "thinks" are the merged cells. Click it again, it takes the text out.

    To sum up/repeat. You can not "locate" a merged by any property of the cell. You have do some sort of calculation.

    In your case, you would first test all the row cell counts, trying to find the row with a different count. That would indicate that the row contains a merged cell. Then test the cells of that row. The problems is...test against what?

    Say the row in question has TWO merged cells. Say they are different widths.

    Can you see that it is a problem?

    VBA hates merged cells, as do I. They are to be avoided as much as possible.

    A cell is what it is.

  3. #3
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,290
    Location
    I'll go further than Gerry and say, simply, that it can't be done.

    You are citing a very simple case but what if all rows had 5 cells and they were just different widths? or if there were also vertically merged cells? or if one cell on the end of one row had simply been deleted? or etc., etc.

    There really isn't any calculation you can do which will give you the answer unless you already all but know it, in which case you can easily make a very specific check for the little bit of information you are missing.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  4. #4
    I was trying to be easy on the poor laddie. There you go, being mean and rude, and spoiling the parade.

    Joking aside, in absolute sense, Tony is correct. The simple example could be done as there is only (I think) one cell merged. So there can be a "control" determined, that you can calculate with. However, it is a very thin piece of logic, and actually technically not true logic at all, because you determine what is the control. You are setting Cell (1,1) as the standard, and comparing everything else to that. Word has no way of knowing if it is true (Cell (1,1) is NOT merged), or not.

    In other words, as Tony put, it can work IF you already know it to be the case. However, that may indeed be the case. In which case, in that specific case, yes, it may be possible to "locate" the merged cell. But.....this is very very sloppy, and could be extremely prone to error. Not something I would recommend, or do for that matter.

    As I stated, VBA hates merged cells, as do I. They are to be avoided.

    In practice, Tony's comment has to be taken very seriously. You could easily have the same cell count per row, but have every single cell a different width.

    You could, say, take ONE row, progammatically make it have equal cell width, get that number, THEN do a comparision, yadda yadda yadda. It just gets uglier and uglier and uglier.

  5. #5
    Try something like this :

    Sub findMerge()

    Dim rng As Range
    Set rng = ActiveSheet.Range("$A$1: $M$30")

    For Each cell In rng.Cells
    cell.Activate
    If ActiveCell.MergeCells = True Then MsgBox (cell.Address & " is merged")
    Next

    End Sub

  6. #6
    Ummmm, except you are giving code for Excel. Not Word, which is what is being discussed. If you ran that in Word - which is what is being discussed - it would fail.

    Excel has cells (ONLY cells). These are structurally very different than Word tables having rows and cells.

  7. #7
    Excel has cells (ONLY cells). These are structurally very different than Word tables having rows and cells.
    Actually, both applications support rows, columns and cells. That said, Word cells are indeed structuraly quite different from Excel cells and, in Word, there is no programmatic (or even visual) method for determining whether a cell has been merged or split (the latter being something Excel cannot do).

    In the OP's original example, there is no way a Word user who did not create the document can tell whether the rows with 5 cells have had one or more cells split or whether the row with 4 cells has had two or more cells merged.
    Cheers
    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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