Does the Selected Range include a merged or split cell?

[Greg Maxey]

That is my 64 million dollar question today.

Some of you may know that I am working on a macro to report table and cell data. Fruit of my labor so far is
http://gregmaxey.mvps.org/Table_Cell_Data.htm

Take the situation where you create a 5x2 table and merge B2 and C2
Then select A1,B1,C1,A2,B2.
If you run TableCellHelper from Macros8.Dot, it will report the selected range as A1:B2. While that may appear correct on the screen {=sum(A1:B2)} will return will not correctly sum the value of the selected cells.

For that reason, I have elected to exclude selection range reporting if the table contains split or merged cells:

If Selection.Tables(1).Uniform Then
Report span
Else
Don't report span
End If

That seems a huge concession. For example if I have a 20x50 table and merge S50 and T50, I have effectively deep sixed range reports for the remaining million or so selection possibilities :-(

Now I am thinking maybe I should just report the range with the disclaimer that if the table is not uniform then the reported range may be in doubt.

After a working lifetime of dedication to trying got put ordnance on target, that seems cheesy ;-)

So, can anyone help me answer the question (in VBA lingo):

If Selected range of cells is free of split or merged cells that would booger up a range report
Report span
Else
Don't report span
End If

As always thanks for your time.

 

[Cooz]

Hi Greg,

If I make a table like you suggest, fill the five cells that are to be
selected with 1 to 5 and select them, the following macro correctly returns
15:

Sub SumCells()
Dim aCell As Cell, lngTotal As Long, strCellVal As String

lngTotal = 0

For Each aCell In Selection.Cells
strCellVal = Left(aCell.Range.Text, Len(aCell.Range.Text) - 2)
lngTotal = lngTotal + CLng(strCellVal)
Next aCell
MsgBox lngTotal

End Sub

Can this be of any use (and if so - am I a rich man?)?

Cooz

 

[Greg]

Cooz,

Thanks for the reply ;-)

I am not looking for a way to sum the cells, that was just an example.
I am looking for a way to evaluate the selection and determine if range
can or can not be accurately reported in an A1:Z100 format. In some
cases it can't be due to merged or split cells.

 

[Tony Jollans]

It really pains me to say this .. but I don't think you can say for certain whether the Selection contains merged cells (assuming the table does, of course) without checking every cell - and maybe some outside the selection as well. Just consider the simple case of a selection of one cell which is a merged cell - what properties does it have that indicate that it is merged?

--
Enjoy,
Tony


That is my 64 million dollar question today.

Some of you may know that I am working on a macro to report table and cell data. Fruit of my labor so far is
http://gregmaxey.mvps.org/Table_Cell_Data.htm

Take the situation where you create a 5x2 table and merge B2 and C2
Then select A1,B1,C1,A2,B2.
If you run TableCellHelper from Macros8.Dot, it will report the selected range as A1:B2. While that may appear correct on the screen {=sum(A1:B2)} will return will not correctly sum the value of the selected cells.

For that reason, I have elected to exclude selection range reporting if the table contains split or merged cells:

If Selection.Tables(1).Uniform Then
Report span
Else
Don't report span
End If

That seems a huge concession. For example if I have a 20x50 table and merge S50 and T50, I have effectively deep sixed range reports for the remaining million or so selection possibilities :-(

Now I am thinking maybe I should just report the range with the disclaimer that if the table is not uniform then the reported range may be in doubt.

After a working lifetime of dedication to trying got put ordnance on target, that seems cheesy ;-)

So, can anyone help me answer the question (in VBA lingo):

If Selected range of cells is free of split or merged cells that would booger up a range report
Report span
Else
Don't report span
End If

As always thanks for your time.

 

[Greg]

Tony,

I am nearing the same conclusion. I suppose as a compromise I could
simply report the range with a condition that it is just a best guess
if the table contains split or merged cells.

 

[Helmut Weber]

Hi everybody,

It really pains me to say this ..

yes, indeed.

"Uniform" tells you nothing else, then whether
the cell-borders match in each row.
(If the numbers of cells if different,
then the cell-borders can't match.)

Just as far as I have been investigating this.

Merging every cell in the second last column
with every cell in the last column results
in a uniform table again.

I think of merged and split cells as a matter
of history of a table. And this history isn't
accessible, or only to a very limited extend.
Via undo, maybe, but only in theory.

I gave up on that question long ago.

One might as well ask, whether a word
contains a deleted character. ;-)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Greg]

Hi Helmut

Now why would anyone pay 64 million dollars for the answer to that
question ;-)