Finding merged cells

[chandansify]

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

Selection.Tables(1).Rows(1).Cells.Count gives 5
Selection.Tables(1).Rows(2).Cells.Count gives 5
Selection.Tables(1).Rows(3).Cells.Count gives 5
Selection.Tables(1).Rows(4).Cells.Count gives 4
Selection.Tables(1).Rows(5).Cells.Count 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? :think:


Thanks in advance.

Chandan

[fumei]

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.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

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.

[TonyJollans]

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.

[fumei]

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.

[sinamit]

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

[fumei]

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.

[macropod]

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.