Can I automate Range.mergeCells = True?

[joeu2004]

In a VBA subroutine, I want to write something like:

Dim r as Range, s as String, n as Long
[...set up r, s and eventually n...]
r.Value = s
r.Resize(1, n).mergeCells = True

The problem is: how can I determine n, the number of colums merge?

My current workaround is to set n to a constant, namely the maximum
number of columns to merge, which I determined by inspection
beforehand. There is nothing to the right of r. I am not happy with
the "by inspection" requirement; I am actually iterating through all
rows in a large worksheet. Moreover, I want n to be the __minimum__
number of columns necessary.

 

[joeu2004]

I learned that my problem statement is not clear to some experienced
VBA programmers. Here is my restatement of the problem. Hope it's
clearer.

Suppose I write:

Range("a1") = "this is a very very very very very long string"
Range("a1").Resize(1, n).mergeCells = True

As I understand it (perhaps incorrectly), I need n to be the number of
columns that A1 covers, assuming there is nothing to the right of A1.

I suspect there is no way to do that. It depends on the current font
type, style and size as well as the current columns widths, which
might vary due to other data in the columns below row 1.

But I thought I would ask others with more VBA programming
experience. Perhaps there is a Range property/method that returns the
number of columns that single-cell contents currently cover.
(Remember: nothing to the right of A1.)

Is there some other way besides Resize(1,n).mergeCells that would
cause Excel to merge across as many columns as it deems necessary?

PS: I need a method that does the merge silently. Empirically, I
learned that range.Merge always(?) pops up a message asking if it's
okay to complete the merge. That's not acceptable. In contrast,
range.mergeCells=True is silent. That's exactly what I want.

 

[GS]

joeu2004 brought next idea :

I learned that my problem statement is not clear to some experienced
VBA programmers. Here is my restatement of the problem. Hope it's
clearer.

Suppose I write:

Range("a1") = "this is a very very very very very long string"
Range("a1").Resize(1, n).mergeCells = True

As I understand it (perhaps incorrectly), I need n to be the number of
columns that A1 covers, assuming there is nothing to the right of A1.

I suspect there is no way to do that. It depends on the current font
type, style and size as well as the current columns widths, which
might vary due to other data in the columns below row 1.

But I thought I would ask others with more VBA programming
experience. Perhaps there is a Range property/method that returns the
number of columns that single-cell contents currently cover.
(Remember: nothing to the right of A1.)

Is there some other way besides Resize(1,n).mergeCells that would
cause Excel to merge across as many columns as it deems necessary?

PS: I need a method that does the merge silently. Empirically, I
learned that range.Merge always(?) pops up a message asking if it's
okay to complete the merge. That's not acceptable. In contrast,
range.mergeCells=True is silent. That's exactly what I want.

This begs me to ask why you want to merge the cells if there's nothing
to the right of A1? The contents should extend as far to the right as
necessary if the 'Wrap Cells' option is NOT turned on. What do you hope
to gain by merging?

 

[joeu2004]

This begs me to ask why you want to merge the cells if there's
nothing to the right of A1?

So that I can select column A and do Autofit based on the other data
in column A, excluding the cells with long text (embedded headings).
For example, column A might look like this:

Group A: This, That And The Other Thing
123.45
567.89

Group B: Other Things and This and That
42.12
1.34

 

[GS]

joeu2004 formulated on Friday :

So that I can select column A and do Autofit based on the other data
in column A, excluding the cells with long text (embedded headings).
For example, column A might look like this:

Group A: This, That And The Other Thing
123.45
567.89

Group B: Other Things and This and That
42.12
1.34

That suggests then, that if you want to autofit ColA including the
cells with long text you want to use something like...

Columns(1).AutoFit

Otherwise, you could find the longest non-text cell via the Len()
function and set AutoFit to size the ColWidth to that cell.

Alternatively, you could use a hidden column to store the length of all
cells that do not contain headings. Then you could check for the cell
with the highest value and AutoFit ColA to that row.

Example: Cells(?, 1).EntireColumn.AutoFit

 

[Gord Dibben]

I haven't tested recently but autofit has never worked with merged cells whether
they be row or column merged.

Takes a whole 'nother set of sheet event code to force the autofitting.

See google search thread for code by Greg Wilson.

http://tinyurl.com/27qe7ql


Gord Dibben MS Excel MVP

 

[GS]

Gord Dibben formulated the question :

I haven't tested recently but autofit has never worked with merged cells
whether they be row or column merged.

Takes a whole 'nother set of sheet event code to force the autofitting.

See google search thread for code by Greg Wilson.

http://tinyurl.com/27qe7ql


Gord Dibben MS Excel MVP

Hi Gord,
Yes, I'm aware of this anomoly and so is why I'm trying to discourage
using merge, letting the long string content flow out over the empty
adjacent cells. Perhaps it's good that you pointed this out as the OP
needs to understand it as well if the intent is to AutoFit.

Personally, I've never had a problem using AutoFit on single cells
where the column also contains merged cells. -The corresponding cell in
the merge resizes accordingly. I think this is what the OP wants to do.

Nevertheless, I rarely have any need to merge cells except when
designing forms on worksheets. Other exceptions are when authoring
manuals that have multi-line paragraphs that wrap text, if I want to
use fill to highlight, or avoid using shapes to display a bordered
block of text or image.