Table Column Spanning Row Question

[George Lee]

I have a table with two columns. The first column has three rows, and the
second column has 10 rows. Obviously, each cell in the first column spans
more than one row. The question is, how can determine how many rows each of
the first column cell’s span?

 

[Pesach Shelnitz]

Hi George,

The following macro will run through the table where your cursor is located
and report the number of rows that each cell in column 1 spans.

Sub ReportRowSpanning()

Dim cel As Cell
Dim inCol2 As Boolean
Dim celNum As Integer
Dim myString As String

inCol2 = False
celNum = 0
myString = ""
With Selection.Tables(1).Range
For Each cel In .Cells
If cel.ColumnIndex = 1 Then
If inCol2 = True Then
myString = myString & "Cell " & celNum & _
" in Column 1 spans " & _
numOfRows & " row(s)." & vbCrLf
End If
inCol2 = False
celNum = celNum + 1
numOfRows = 0
ElseIf cel.ColumnIndex = 2 Then
inCol2 = True
numOfRows = numOfRows + 1
End If
Next
MsgBox myString & "Cell " & celNum & " in Column 1 spans " _
& numOfRows & " row(s)."
End With
End Sub

If you want to use this information in code, write back more details about
how you want to use it.

 

[Fumei2]

"Obviously, each cell in the first column spans
more than one row."

Unfortunately, this is not correct. The cell does NOT span more than one row.
The cell is on one row. Period.

When you vertically merged the cells in column 1 (no matter what number of
rows you used to merge), they become merged and thus one row.

Merged rows like this are very difficult to proces with VBA. In fact, VBA
does not like this at all, and will return a 5991 error:

"Can not access individual rows in this collection because the table has
vertically merged cells."

Just to make it clear, say you have Table_A:

Col 1 Col 2

Row 1 Row 1
Row 2
Row 2 Row 3
Row 4
Row 5
Row 3 Row 6
Row 7
Row 8
Row 9
Row 10

and Table_B:

Col 1 Col 2

Row 1 Row 1
Row 2
Row 3
Row 4
Row 2 Row 5
Row 6
Row 7
Row 8
Row 3 Row 9
Row 10

Table_A Row 1 - in your terms - "spans" TWO rows.
Table_B Row 1 - in your terms - "spans" FOUR rows.

Except... it does not really do that. There is no way to get those numbers
(TWO and FOUR). Well, at least not easily. It would take an immense amount
of fussing code (trust me on this...you do not want to go there).

 

[Fumei2]

Pesach, nice code.

Hi George,

The following macro will run through the table where your cursor is located
and report the number of rows that each cell in column 1 spans.

Sub ReportRowSpanning()

Dim cel As Cell
Dim inCol2 As Boolean
Dim celNum As Integer
Dim myString As String

inCol2 = False
celNum = 0
myString = ""
With Selection.Tables(1).Range
For Each cel In .Cells
If cel.ColumnIndex = 1 Then
If inCol2 = True Then
myString = myString & "Cell " & celNum & _
" in Column 1 spans " & _
numOfRows & " row(s)." & vbCrLf
End If
inCol2 = False
celNum = celNum + 1
numOfRows = 0
ElseIf cel.ColumnIndex = 2 Then
inCol2 = True
numOfRows = numOfRows + 1
End If
Next
MsgBox myString & "Cell " & celNum & " in Column 1 spans " _
& numOfRows & " row(s)."
End With
End Sub

If you want to use this information in code, write back more details about
how you want to use it.

 

[George Lee]

Excellent! I never thought of approaching it that way.