I've seen a number of posts here and there that it is possible create one's
auto fit for
merged cells in a row to adjust cell height,

The logic is straightforward, but the execution is very slow.

Granted, I am using a 10 year old computer.
Yes, I will get another computer, indeed, I intend to build the critter and
purchased Win XP Pro SP@ on 15 April.

In any case, any ideas on how to speed up the code below.
On my old PC, it takes about 1 second to exceute the code for a cell merged
area.

Private Sub AutoFitMergeArea(rngSource As Excel.Range)
' Performs row height autofit for the MergeArea
' including rngSource

' Range must contain only 1 row.
' WrapText must be set to True.

' Row height is not reduced because other cells in the same row
' may need a greater height.

Dim MergedAreaWidth As Single
Dim NewRowHeight As Single
Dim rngCell As Excel.Range
Dim SourceWidth As Single
Dim SourceRowHeight As Single

With rngSource
If .MergeCells Then
MergedAreaWidth = 0#
SourceWidth = .Columns(1).ColumnWidth
If .Rows.count = 1 And .WrapText Then
SourceRowHeight = .RowHeight
For Each rngCell In rngSource
MergedAreaWidth = rngCell.ColumnWidth + MergedAreaWidth
Next rngCell
.MergeCells = vbFalse
.Cells(1).ColumnWidth = MergedAreaWidth
.EntireRow.AutoFit
NewRowHeight = .RowHeight
.Cells(1).ColumnWidth = SourceWidth
.MergeCells = vbTrue
If SourceRowHeight NewRowHeight Then
.RowHeight = SourceRowHeight
Else
.RowHeight = NewRowHeight
End If
End If
End If
End With
Set rngCell = Nothing
End Sub

Corrections

Granted, I am using a 10 year old computer.
Yes, I will get another computer, indeed, I intend to build the critter
and
purchased Win XP Pro SP@ on 15 April.

SP@ should be SP2.

In any case, any ideas on how to speed up the code below.
On my old PC, it takes about 1 second to exceute the code for a cell
merged
area.

"cell merged" should be "2 cell merged".

The posted code is missing "Application.ScreenUpdating = False" at the
beginning and "Application.ScreenUpdating = True" at the end. Since you
implied you understand the code, I'll let you decide where to insert the
lines.

Greg

"Howard Kaikow" wrote:

I've seen a number of posts here and there that it is possible create one's
auto fit for
merged cells in a row to adjust cell height,

The logic is straightforward, but the execution is very slow.

Granted, I am using a 10 year old computer.
Yes, I will get another computer, indeed, I intend to build the critter and
purchased Win XP Pro SP@ on 15 April.

In any case, any ideas on how to speed up the code below.
On my old PC, it takes about 1 second to exceute the code for a cell merged
area.

Private Sub AutoFitMergeArea(rngSource As Excel.Range)
' Performs row height autofit for the MergeArea
' including rngSource

' Range must contain only 1 row.
' WrapText must be set to True.

' Row height is not reduced because other cells in the same row
' may need a greater height.

Dim MergedAreaWidth As Single
Dim NewRowHeight As Single
Dim rngCell As Excel.Range
Dim SourceWidth As Single
Dim SourceRowHeight As Single

With rngSource
If .MergeCells Then
MergedAreaWidth = 0#
SourceWidth = .Columns(1).ColumnWidth
If .Rows.count = 1 And .WrapText Then
SourceRowHeight = .RowHeight
For Each rngCell In rngSource
MergedAreaWidth = rngCell.ColumnWidth + MergedAreaWidth
Next rngCell
.MergeCells = vbFalse
.Cells(1).ColumnWidth = MergedAreaWidth
.EntireRow.AutoFit
NewRowHeight = .RowHeight
.Cells(1).ColumnWidth = SourceWidth
.MergeCells = vbTrue
If SourceRowHeight NewRowHeight Then
.RowHeight = SourceRowHeight
Else
.RowHeight = NewRowHeight
End If
End If
End If
End With
Set rngCell = Nothing
End Sub

Actually the logic is not straightforward if you need to deal with
mergearea's that contain both multiple columns and rows. That aside, the
code you posted could be improved though only to small effect compared with
what will always be the reletively slow step of re-merging. However your
code as posted fails to do that, unless I understand the purpose I assume
that's an oversite.

I did try your function and in my (I guess) equally old system it took an
eyeblink. If it took a second in yours there's something else going on.

If you have many potential mergeareas to autofit it would be very
significantly faster to process on another sheet and not need to do
re-merge's at all. Here's an example that gives you the option to do it both
ways, note the process is an entire row at a time. Watch out wrap'd code

Option Explicit
Sub test()
Dim cel As Range
Dim ws As Worksheet
Dim t As Single
t = Timer

Application.ScreenUpdating = False
For Each cel In Selection.Columns(1).Cells
'only send one cell per row
AutoFitMergeRow cel, False, ws
Next

If Not ws Is Nothing Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = False
End If
Application.ScreenUpdating = True
Debug.Print Timer - t ' res about 1/18 sec
End Sub

Sub AutoFitMergeRow(rSource As Range, bInPlace As Boolean, Optional wsDummy
As Worksheet)
Dim bDelDummy As Boolean
Dim bScrUDate As Boolean
Dim i As Long, j As Long, k As Long, cc As Long, w As Single, rw As Long
Dim rng As Range, cel As Range, rSourceRow As Range
Dim shtOrig As Object
Dim nTop As Long, nBot As Long
Dim arMA() As Range


bScrUDate = Application.ScreenUpdating
Set rSourceRow = rSource(1).EntireRow.Cells

If Not IsNull(rSourceRow.MergeCells) Then
rSource.Rows(1).AutoFit ' no merged cells
Else

If Not bInPlace Then
If wsDummy Is Nothing Then
With rSource.Parent.Parent
On Error Resume Next
Set wsDummy = .Worksheets("Dummy")
On Error GoTo 0
If wsDummy Is Nothing Then
bDelDummy = IsMissing(wsDummy)

Set wsDummy =
..Worksheets.Add(after:=.Sheets(.Sheets.Count))
wsDummy.Name = "Dummy"
rSource.Parent.Activate

End If
End With
End If

Set rng = wsDummy.Rows(1).Cells

' NOTE this fails to copy if the
' contains merged areas in rows
rSourceRow.Cells.Copy rng

End If

If bInPlace Then
Set rng = Intersect(rSourceRow.Parent.UsedRange,
rSourceRow.Cells)
Else
Set rng = Intersect(wsDummy.UsedRange, rng.Cells)
End If


If Not rng Is Nothing Then
i = rng.Columns(1).Column - 1

ReDim aw(i To rng.Columns.Count + i)
If bInPlace Then
ReDim arMA(i To UBound(aw)) As Range
End If

For Each cel In rng
If cel.Column i Then

cc = cel.MergeArea.Columns.Count

If cc 1 Then
If cel.Address = cel.MergeArea(1).Address Then

w = 0
If bInPlace Then
aw(i) = cel.ColumnWidth
Set arMA(i) = cel.MergeArea
End If

For j = 1 To cc
w = w + rSourceRow.Cells(1, i +
j).ColumnWidth
Next
cel.ColumnWidth = w

End If
End If
i = i + cc
' Else
' 'skip other cells in mergearea
End If

Next

rng.MergeCells = False
End If

If Not bInPlace Then
wsDummy.Rows(1).AutoFit
rSource.RowHeight = wsDummy.Rows(1).RowHeight

wsDummy.Rows(1).Clear
wsDummy.Rows(1).EntireRow.Delete
Else
rSource.Rows(1).AutoFit
For i = LBound(aw) To UBound(aw)
k = k + 1
If Not arMA(i) Is Nothing Then
rng(k).ColumnWidth = aw(i)
arMA(i).Merge
End If
Next
End If
End If
Application.ScreenUpdating = bScrUDate
End Sub

In my ancient setup time to process 10 rows each containing 50 mergeareas
each with several lines of text (a mixture and wordwrap'd), took 0.828125
seconds in total.

Note as written the above does not cater for mergeareas that include
multiple rows.

Regards,
Peter T


"Howard Kaikow" wrote in message
...
I've seen a number of posts here and there that it is possible create
one's
auto fit for
merged cells in a row to adjust cell height,

The logic is straightforward, but the execution is very slow.

Granted, I am using a 10 year old computer.
Yes, I will get another computer, indeed, I intend to build the critter
and
purchased Win XP Pro SP@ on 15 April.

In any case, any ideas on how to speed up the code below.
On my old PC, it takes about 1 second to exceute the code for a cell
merged
area.

Private Sub AutoFitMergeArea(rngSource As Excel.Range)
' Performs row height autofit for the MergeArea
' including rngSource

' Range must contain only 1 row.
' WrapText must be set to True.

' Row height is not reduced because other cells in the same row
' may need a greater height.

Dim MergedAreaWidth As Single
Dim NewRowHeight As Single
Dim rngCell As Excel.Range
Dim SourceWidth As Single
Dim SourceRowHeight As Single

With rngSource
If .MergeCells Then
MergedAreaWidth = 0#
SourceWidth = .Columns(1).ColumnWidth
If .Rows.count = 1 And .WrapText Then
SourceRowHeight = .RowHeight
For Each rngCell In rngSource
MergedAreaWidth = rngCell.ColumnWidth +
MergedAreaWidth
Next rngCell
.MergeCells = vbFalse
.Cells(1).ColumnWidth = MergedAreaWidth
.EntireRow.AutoFit
NewRowHeight = .RowHeight
.Cells(1).ColumnWidth = SourceWidth
.MergeCells = vbTrue
If SourceRowHeight NewRowHeight Then
.RowHeight = SourceRowHeight
Else
.RowHeight = NewRowHeight
End If
End If
End If
End With
Set rngCell = Nothing
End Sub

"Greg Wilson" wrote in message
...
The posted code is missing "Application.ScreenUpdating = False" at the
beginning and "Application.ScreenUpdating = True" at the end. Since you
implied you understand the code, I'll let you decide where to insert the
lines.

Actually, I do have the code, but it is commented out.
Code is not necessary, as Excel will not be visible.

"Peter T" <peter_t@discussions wrote in message
...
Actually the logic is not straightforward if you need to deal with
mergearea's that contain both multiple columns and rows.

The code explicitly limits itself to 1 row,

That aside, the
code you posted could be improved though only to small effect compared
with
what will always be the reletively slow step of re-merging. However your
code as posted fails to do that, unless I understand the purpose I assume
that's an oversite.

I do not understand the above comment.

I did try your function and in my (I guess) equally old system it took an
eyeblink. If it took a second in yours there's something else going on.

Mine is really 10 years old, Pentium II 400 mhz.

Hmmm, I wonder if the AV software is interfering.
After I get off the internet, I'll disable the AV and see what happens.

If you have many potential mergeareas to autofit it would be very
significantly faster to process on another sheet and not need to do
re-merge's at all.

I had thought about doing that.

Here's an example that gives you the option to do it both
ways, note the process is an entire row at a time. Watch out wrap'd code

At this time, I am not processing a row at a time, rather 2 merged cells at
a time.
I may process a row at a time after I decide the final layout of the cells.

The AV software does play a part in the absolute time.

But the relative time increase when adjusting for row height
appears to be quite similar whether or not the AV is enabled..

Running the program from a .exe.

With AV disabled:
No adjustment for row height: 19 seconds
Adjustment for row height: 36 seconds


With AV Enabled:
No adjustment for Row height: 25 seconds
No adjustment for row height: 24 seconds

Adjustment for Row height: 50 seconds
Adjustment for row height: 47 seconds

"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
Actually the logic is not straightforward if you need to deal with
mergearea's that contain both multiple columns and rows.

The code explicitly limits itself to 1 row,

It appears to be limited to one cell.


That aside, the
code you posted could be improved though only to small effect compared
with
what will always be the reletively slow step of re-merging. However your
code as posted fails to do that, unless I understand the purpose I
assume
that's an oversite.

I do not understand the above comment.

Maybe it's me that doesn't understand the purpose of your code. I assumed
the idea was to size a row height to the "autofit" height of a mergearea
consisting of two or more cells in a row (subject not making the row height
less than it was). But your code doesn't do that at all. Instead it unmerges
the mergarea, does not resize the width of cell(1) to the original mergearea
width, then autofits row height, does not re-merge the original mergearea.
If that's the intention I don't get it.


I did try your function and in my (I guess) equally old system it took
an
eyeblink. If it took a second in yours there's something else going on.

Mine is really 10 years old, Pentium II 400 mhz.

The one I tested on is similar but a tad slower, PII 350.

Hmmm, I wonder if the AV software is interfering.
After I get off the internet, I'll disable the AV and see what happens.

I'd be alarmed to think AV might make such a difference, hope it's not that.

If you have many potential mergeareas to autofit it would be very
significantly faster to process on another sheet and not need to do
re-merge's at all.

I had thought about doing that.

Here's an example that gives you the option to do it both
ways, note the process is an entire row at a time. Watch out wrap'd code

At this time, I am not processing a row at a time, rather 2 merged cells
at
a time.
I may process a row at a time after I decide the final layout of the
cells.

Why not try the example I posted with a large number of cells in multiple
rows. Select cells in a column that intersects those rows and run as is.

In your reply to Greg you say you don't disable screenupdating as the
instance is not visible. Even not visible it's still worth disabling.

Regards,
Peter T

I didn't see this when I posted a few minutes ago. I wonder if the
differences with AV enabled/disabled relates to AV doing it's own thing in
the background, or directly poking its nose into what you are doing.

I don't know what you are processing but whatever it is it seems like a very
long time.

Regards,
Peter T

"Howard Kaikow" wrote in message
...
The AV software does play a part in the absolute time.

But the relative time increase when adjusting for row height
appears to be quite similar whether or not the AV is enabled..

Running the program from a .exe.

With AV disabled:
No adjustment for row height: 19 seconds
Adjustment for row height: 36 seconds


With AV Enabled:
No adjustment for Row height: 25 seconds
No adjustment for row height: 24 seconds

Adjustment for Row height: 50 seconds
Adjustment for row height: 47 seconds

"Peter T" <peter_t@discussions wrote in message
...
I didn't see this when I posted a few minutes ago. I wonder if the
differences with AV enabled/disabled relates to AV doing it's own thing in
the background, or directly poking its nose into what you are doing.

Sigh, I just checke the AV log file.
Alas, it refreshed the event log shortly after I ran the program, so I
cannot what events were going on in the AV.


I don't know what you are processing but whatever it is it seems like a
very
long time.

Yes, sumptin's not right.

I may try this with Excel 97, 2000 and 2002 to see if the same behavior is
exhibited.

"Peter T" <peter_t@discussions wrote in message
...
It appears to be limited to one cell.
It is doing the MergeArea in which the cell lives.

Maybe it's me that doesn't understand the purpose of your code. I assumed
the idea was to size a row height to the "autofit" height of a mergearea
consisting of two or more cells in a row (subject not making the row
height
less than it was).

Yes.

But your code doesn't do that at all. Instead it unmerges
the mergarea, does not resize the width of cell(1) to the original
mergearea
width, then autofits row height, does not re-merge the original mergearea.
If that's the intention I don't get it.

The code is doing the intended deed.
It unmerges so it can set the width of col 1 to the width of the original
col 1 + col 2;
Then it does an AutoFit with the unmereged cells.
then it sets the rowheight.
then it sets the width of column 1 back to the original width, then it
merges,
and, adjusts the row height.

The one I tested on is similar but a tad slower, PII 350.

This is a multibbot system, I'll boot to an OS with Excel 97, but first
gotta have some OJ, etc.!

In your reply to Greg you say you don't disable screenupdating as the
instance is not visible. Even not visible it's still worth disabling.


I tried Screenupdating, as expected, with a non-visible wbk, may actually
have slowed down things.

"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
It appears to be limited to one cell.
It is doing the MergeArea in which the cell lives.

<snip

The code is doing the intended deed.
It unmerges so it can set the width of col 1 to the width of the original
col 1 + col 2;
Then it does an AutoFit with the unmereged cells.
then it sets the rowheight.
then it sets the width of column 1 back to the original width, then it
merges,
and, adjusts the row height.

OK I get it now, it works as you say providing the entire MergeArea is
passed to the function (which means of course you need to know in advance
it's full meargearea) or pass say activecell.mergearea. Previously I had
only been passing a single cell and so it didn't do as expected.

I tried Screenupdating, as expected, with a non-visible wbk, may actually
have slowed down things.

I did a quick test of timings writing text to a lot of cells in an automated
instance.

xlapp.visible = false
47.51172 SU=True
40.48047 SU=False

xlapp.visible = true
65.1875 SU=True
40.32031 SU=False

time in seconds, SU refers to screenupdating)

Regards,
Peter T

OK I get it now, it works as you say providing the entire MergeArea is
passed to the function (which means of course you need to know in advance
it's full meargearea) or pass say activecell.mergearea. Previously I had
only been passing a single cell and so it didn't do as expected.

I had a version that used a single cell, but that would be inefficient, as
I already know the merge area.

Now that I've modified the code to work on all RELEVANT cells with a row,
I can get a great increase in speed by doing the following:

1. Do NOT wrap or merge cells vefore calling the code.
2. Work off of only the first cell in the range that is to be merged.

I was wondering whether using a Union, as below, is faster.
I need to, perhaps, tweak the code for handling less than 4 critters
in a row.

with SomeRangeObject
' If Union is faster, then other tweaks can be made below.
Set rng = Union(.Offset(r, 0), .Offset(r, 2), .Offset(r, 4),
..Offset(r, 6))
' The following is not needed as I am creating the workbook
anew.
' SourceRowHeight = .RowHeight
'The following is really a constant for the entire workbook
being created anew.
SourceWidth = .Offset(r, 0).Columns(1).ColumnWidth
MergedAreaWidth = 2 * SourceWidth
rng.ColumnWidth = MergedAreaWidth
' .Offset(r, 0).ColumnWidth = MergedAreaWidth
' .Offset(r, 2).ColumnWidth = MergedAreaWidth
' .Offset(r, 4).ColumnWidth = MergedAreaWidth
' .Offset(r, 6).ColumnWidth = MergedAreaWidth
rng.WrapText = vbTrue
' .Offset(r, 0).WrapText = vbTrue
' .Offset(r, 2).WrapText = vbTrue
' .Offset(r, 4).WrapText = vbTrue
' .Offset(r, 6).WrapText = vbTrue
' .Offset(r, 0).EntireRow.AutoFit
NewRowHeight = .Offset(r, 0).Rows(1).RowHeight
rng.ColumnWidth = SourceWidth
' .Offset(r, 0).ColumnWidth = SourceWidth
' .Offset(r, 2).ColumnWidth = SourceWidth
' .Offset(r, 4).ColumnWidth = SourceWidth
' .Offset(r, 6).ColumnWidth = SourceWidth
For new w orkbook, not an issue.
' If SourceRowHeight NewRowHeight Then
' .Offset(r, 0).Rows(1).RowHeight = SourceRowHeight
' Else
.Offset(r, 0).Rows(1).RowHeight = NewRowHeight
' End If
.Application.Range(.Offset(r, 0), .Offset(r, 1)).MergeCells
= vbTrue
.Application.Range(.Offset(r, 2), .Offset(r, 3)).MergeCells
= vbTrue
.Application.Range(.Offset(r, 4), .Offset(r, 5)).MergeCells
= vbTrue
.Application.Range(.Offset(r, 6), .Offset(r, 7)).MergeCells
= vbTrue
End With

When you run the code, are you running via VB 6, or are you running in
Excel?

"Howard Kaikow" wrote in message
...
I was wondering whether using a Union, as below, is faster.

Using a sample size of 1 run, Union is much faster, 30 seconds rather than
39 seconds.

"Howard Kaikow" wrote in message
...
"Howard Kaikow" wrote in message
...
I was wondering whether using a Union, as below, is faster.

Using a sample size of 1 run, Union is much faster, 30 seconds rather than
39 seconds.

Difficult to comment without context of what you are doing overall. I take
it you are processing a lot of cells.

Union is pretty quick with a small number of areas but rapidly becomes
exponentially slower with more. Note it is the number of resulting areas
rather than number of unions that's relevant. It's a little bit faster to
make a multi-area range with an address. Limited to 255 characters or say 16
multcell areas located anywhere on the sheet and $'s trimmed (would need to
recalc the max safe qty in xl2007). All this is of little consequence with
only four areas as you appear to have. Except of course having built the
multi area it's then faster to work with rather than each individual area,
as you have found.

There's a difference in how the range is made with address vs union which
might be of relevance to you (apart from small speed gain). Namely you can
build adjacent multi-areas. So for your purposes you could replace four
MergeCells = True, that relate to adjacent areas, with just one mergecells =
true. If that doesn't make sense I'll post an example.

I can't help but wonder if your current 30 seconds might much less. Guess it
depends on what you are doing.

Regards,
Peter T

"Howard Kaikow" wrote in message
...
When you run the code, are you running via VB 6, or are you running in
Excel?


In Word. I 've just repeated in VB6 and got similar timings. However, seems
it depends on what you are doing as to whether there's a significant speed
gain with screenupdating disabled when excel is not visible.

Try the following in Word or VB6 and have a cup of coffee, or even a glass
of OJ. For me a significant saving with bAutoFitTest = false but not much in
the second loop when bAutoFitTest = True

Option Explicit
Sub xlScrnUpdate()
Dim bAutoFitTest As Boolean
Dim i As Long
Dim t As Single
Dim oRng As Object
Dim oWS As Object
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
Set oWS = xlApp.workbooks.Add.worksheets(1)

For i = 0 To 1

bAutoFitTest = i = 1
Debug.Print bAutoFitTest

xlApp.Visible = False
xlApp.ScreenUpdating = True
testStuff oWS, bAutoFitTest

xlApp.ScreenUpdating = False
testStuff oWS, bAutoFitTest

xlApp.Visible = True
xlApp.ScreenUpdating = True
testStuff oWS, bAutoFitTest

xlApp.ScreenUpdating = False
testStuff oWS, bAutoFitTest
Next
xlApp.ScreenUpdating = True

Stop

oWS.Parent.Close 0
Set oWS = Nothing
xlApp.Quit
Set xlApp = Nothing
Unload Me

End Sub

Sub testStuff(oWS As Object, bColWidth As Boolean)
Dim i As Long, qty As Long
Dim t As Single

If bColWidth Then qty = 2000 Else qty = 10000

oWS.Cells.clearcontents
With oWS.Columns(1)
.columnwidth = .Parent.standardwidth
t = Timer
For i = 1 To qty
.Cells(i, 1) = i & " some text longish text " & i
If bColWidth Then
.entirecolumn.AutoFit
End If
Next
End With
t = Timer - t
Debug.Print t
End Sub

Private Sub Form_Load()
xlScrnUpdate
End Sub

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
"Howard Kaikow" wrote in message
...
When you run the code, are you running via VB 6, or are you running in
Excel?

Ah, you are aitomating Excel from Word.
That is likely significantly different than automating from VB 6.

Not to mention AV software might poke its nose in differently.

Also, I forget to mention that while I am running my "real" program, I
outputing progress both
to a listbox and a text file.

However, the latest version of hte code, used inline, rather than via sub,
eliminates a lot of the overhead, e.g., it does not do the mergearea until
after everything else is done, and uses a Union which is clearly faster.

In my original example, I was processing 1 chunk at a time, moving down a
column.
In the "real" program, I've changed that to process along rows.

Code snippett is given below.

In Word. I 've just repeated in VB6 and got similar timings. However,
seems
it depends on what you are doing as to whether there's a significant speed
gain with screenupdating disabled when excel is not visible.

I did extensive testing of Screenupdating in Word over the years, there is a
significant
improvement, using the Range object with Word, more so if SCreenupdating is
not enabled.
Of course the document is not visible.

IN my case, enabling screenupdaing adversely affects performance, no need to
test otherwise.

k = count Mod PerRow
'If symbols are used, no need to adjust rowheight
If Not bUseSymbols Then
Select Case k
Case 1
Set rng = .Offset(r, 0)
Case 2
Set rng = Union(.Offset(r, 0), .Offset(r, 2))
Case 3
Set rng = Union(.Offset(r, 0), .Offset(r, 2),
..Offset(r, 4))
Case 4
Set rng = Union(.Offset(r, 0), .Offset(r, 2),
..Offset(r, 4), .Offset(r, 6))
End Select
SourceWidth = .Offset(r, 0).Columns(1).ColumnWidth
MergedAreaWidth = 2 * SourceWidth
With rng
.ColumnWidth = MergedAreaWidth
.WrapText = vbTrue
With .Rows(1)
.EntireRow.AutoFit
.RowHeight = .RowHeight
End With
.ColumnWidth = SourceWidth
End With
End If
For j = 1 To k
n = (j - 1) * 2 ' I yam merging 2 cells
.Application.Range(.Offset(r, n), .Offset(r, n +
1)).MergeCells = vbTrue
Next j

"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
"Howard Kaikow" wrote in message
...
When you run the code, are you running via VB 6, or are you running in
Excel?

Ah, you are aitomating Excel from Word.
That is likely significantly different than automating from VB 6.

Like I said, not significantly different at all in Word vs VB6, in fact very
similar as I would have expected. Some things of course work faster in
compiled VB, though not much difference working with excel objects, even in
the IDE (FWIW I've noticed an in process dll can be faster, even than the
same dll used as a Com addin).

Not to mention AV software might poke its nose in differently.

No idea about that.


Also, I forget to mention that while I am running my "real" program, I
outputing progress both
to a listbox and a text file.

However, the latest version of hte code, used inline, rather than via sub,
eliminates a lot of the overhead, e.g., it does not do the mergearea until
after everything else is done, and uses a Union which is clearly faster.

In my original example, I was processing 1 chunk at a time, moving down a
column.
In the "real" program, I've changed that to process along rows.

Code snippett is given below.

I take it you didn't try the litte test I posted (which demonstrates the
gain to be had by disabling screenupdating in an invisible instance depends
on what you are doing to sheet and cells). Your snippet is sort of simlar to
the second of my two tests which showed only a small gain to be had by
disabling screenupdating in an invisible instance.

In Word. I 've just repeated in VB6 and got similar timings. However,
seems
it depends on what you are doing as to whether there's a significant
speed
gain with screenupdating disabled when excel is not visible.

I did extensive testing of Screenupdating in Word over the years, there is
a
significant
improvement, using the Range object with Word, more so if SCreenupdating
is
not enabled.
Of course the document is not visible.

I didn't test anything relating to Word and Word's Range object, other than
automating Excel in Word.

IN my case, enabling screenupdaing adversely affects performance, no need
to
test otherwise.

Of course go with whatever works best with your overall scenario. Just
looking at the snippet below I'd bet it would be a tad faster with
screenupdating disabled in an invisible instance. Other things you are doing
might negate that.


k = count Mod PerRow
'If symbols are used, no need to adjust rowheight
If Not bUseSymbols Then
Select Case k
Case 1
Set rng = .Offset(r, 0)
Case 2
Set rng = Union(.Offset(r, 0), .Offset(r, 2))
Case 3
Set rng = Union(.Offset(r, 0), .Offset(r, 2),
.Offset(r, 4))
Case 4
Set rng = Union(.Offset(r, 0), .Offset(r, 2),
.Offset(r, 4), .Offset(r, 6))
End Select
SourceWidth = .Offset(r, 0).Columns(1).ColumnWidth
MergedAreaWidth = 2 * SourceWidth
With rng
.ColumnWidth = MergedAreaWidth
.WrapText = vbTrue
With .Rows(1)
.EntireRow.AutoFit
.RowHeight = .RowHeight
End With
.ColumnWidth = SourceWidth
End With
End If
For j = 1 To k
n = (j - 1) * 2 ' I yam merging 2 cells
.Application.Range(.Offset(r, n), .Offset(r, n +
1)).MergeCells = vbTrue
Next j

"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
"Howard Kaikow" wrote in message
...
When you run the code, are you running via VB 6, or are you running in
Excel?

Ah, you are aitomating Excel from Word.
That is likely significantly different than automating from VB 6.

Like I said, not significantly different at all in Word vs VB6, in fact very
similar as I would have expected. Some things of course work faster in
compiled VB, though not much difference working with excel objects, even in
the IDE (FWIW I've noticed an in process dll can be faster, even than the
same dll used as a Com addin).

Not to mention AV software might poke its nose in differently.

No idea about that.


Also, I forget to mention that while I am running my "real" program, I
outputing progress both
to a listbox and a text file.

However, the latest version of hte code, used inline, rather than via sub,
eliminates a lot of the overhead, e.g., it does not do the mergearea until
after everything else is done, and uses a Union which is clearly faster.

In my original example, I was processing 1 chunk at a time, moving down a
column.
In the "real" program, I've changed that to process along rows.

Code snippett is given below.

I take it you didn't try the litte test I posted (which demonstrates the
gain to be had by disabling screenupdating in an invisible instance depends
on what you are doing to sheet and cells). Your snippet is sort of simlar to
the second of my two tests which showed only a small gain to be had by
disabling screenupdating in an invisible instance.

In Word. I 've just repeated in VB6 and got similar timings. However,
seems
it depends on what you are doing as to whether there's a significant
speed
gain with screenupdating disabled when excel is not visible.

I did extensive testing of Screenupdating in Word over the years, there is
a
significant
improvement, using the Range object with Word, more so if SCreenupdating
is
not enabled.
Of course the document is not visible.

I didn't test anything relating to Word and Word's Range object, other than
automating Excel in Word.

IN my case, enabling screenupdaing adversely affects performance, no need
to
test otherwise.

Of course go with whatever works best with your overall scenario. Just
looking at the snippet below I'd bet it would be a tad faster with
screenupdating disabled in an invisible instance. Other things you are doing
might negate that.


k = count Mod PerRow
'If symbols are used, no need to adjust rowheight
If Not bUseSymbols Then
Select Case k
Case 1
Set rng = .Offset(r, 0)
Case 2
Set rng = Union(.Offset(r, 0), .Offset(r, 2))
Case 3
Set rng = Union(.Offset(r, 0), .Offset(r, 2),
.Offset(r, 4))
Case 4
Set rng = Union(.Offset(r, 0), .Offset(r, 2),
.Offset(r, 4), .Offset(r, 6))
End Select
SourceWidth = .Offset(r, 0).Columns(1).ColumnWidth
MergedAreaWidth = 2 * SourceWidth
With rng
.ColumnWidth = MergedAreaWidth
.WrapText = vbTrue
With .Rows(1)
.EntireRow.AutoFit
.RowHeight = .RowHeight
End With
.ColumnWidth = SourceWidth
End With
End If
For j = 1 To k
n = (j - 1) * 2 ' I yam merging 2 cells
.Application.Range(.Offset(r, n), .Offset(r, n +
1)).MergeCells = vbTrue
Next j

Sorry about the double post.

"Peter T" <peter_t@discussions wrote in message

Of course go with whatever works best with your overall scenario. Just
looking at the snippet below I'd bet it would be a tad faster with
screenupdating disabled in an invisible instance. Other things you are
doing
might negate that.

Thought I'd better check and disabling screenupdating in an invisble
instance was just as I expected, a tad faster.

I poulated A1:A100 with some text then ran your code in a loop like this

With r1 ' .range("A1")
For r = 1 To 100
k = k + 1
If k = 5 Then k = 1
' k = Count Mod PerRow
'If symbols are used, no need to adjust rowheight
If Not bUseSymbols Then ' false
Select Case k

I don't understand why adjust you columnwidth's in each loop
..ColumnWidth = MergedAreaWidth
then
..ColumnWidth = SourceWidth
if indeed that's what you are doing, each time in a long loop

I adusted the widths in all 4 columns before start of loop then reset after
the loop. A significant speed gain. Also got another speed gain by merging
multiple areas in one go, vs your For j = 1 To k loop (I refered to the
method in an adjacent post).

Regards,
Peter T


k = count Mod PerRow
'If symbols are used, no need to adjust rowheight
If Not bUseSymbols Then
Select Case k
Case 1
Set rng = .Offset(r, 0)
Case 2
Set rng = Union(.Offset(r, 0), .Offset(r, 2))
Case 3
Set rng = Union(.Offset(r, 0), .Offset(r, 2),
.Offset(r, 4))
Case 4
Set rng = Union(.Offset(r, 0), .Offset(r, 2),
.Offset(r, 4), .Offset(r, 6))
End Select
SourceWidth = .Offset(r, 0).Columns(1).ColumnWidth
MergedAreaWidth = 2 * SourceWidth
With rng
.ColumnWidth = MergedAreaWidth
.WrapText = vbTrue
With .Rows(1)
.EntireRow.AutoFit
.RowHeight = .RowHeight
End With
.ColumnWidth = SourceWidth
End With
End If
For j = 1 To k
n = (j - 1) * 2 ' I yam merging 2 cells
.Application.Range(.Offset(r, n), .Offset(r, n +
1)).MergeCells = vbTrue
Next j

"Peter T" <peter_t@discussions wrote in message
...
I take it you didn't try the litte test I posted (which demonstrates the
gain to be had by disabling screenupdating in an invisible instance
depends
on what you are doing to sheet and cells). Your snippet is sort of simlar
to
the second of my two tests which showed only a small gain to be had by
disabling screenupdating in an invisible instance.

I'll try that at a later time

"Peter T" <peter_t@discussions wrote in message
...
I don't understand why adjust you columnwidth's in each loop
.ColumnWidth = MergedAreaWidth
then
.ColumnWidth = SourceWidth
if indeed that's what you are doing, each time in a long loop

The final code will have such efficiencies.

FYI, .I finally replaced Union with Areas.

Time was cut in about half.

Tho, I now have to find out the string limits for Areas in each version of
Excel

"Howard Kaikow" wrote in message
FYI, .I finally replaced Union with Areas.

Time was cut in about half.

I'd be surprised if it replacing Union with Address alone cut your time in
half, but for sure doing multiple merge's in one go would have helped. I had
some range related code that took some minutes to complete. It was a matter
of chippping away and eventually time was reduced to just a few seconds.
However what was once a few lines became several hundred to do the same
thing.

Tho, I now have to find out the string limits for Areas in each version of
Excel

Len("AA12345:AB12345,") = 16
16 x 16 less one comma = 255

Address limit is 255 in '97 to 2003, up to 16 areas anywhere on the sheet.

I don't know what it is in 2007, probably also 255. However due to increased
sheet size a single area address length can be longer, and the 'safe' number
of areas less.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Address limit is 255 in '97 to 2003, up to 16 areas anywhere on the sheet.

The limit is not 16 areas, I am currently using 25.

Also, the address limit is higher in Excel 97 than in Excel 2003, see code
below.
I ran the code in both 97 and 2003.

I have to dynamically create the string, so I can use On Error to see
whether I have to process in chunks. Code below adds 4 areas at a time. I
will refine to get accurate number.

Private Sub AreasString()
Dim count As Long
Dim high As Long
Dim r As Long
Dim i As Long
Dim sArea As String
Dim rng As Excel.Range

Const DescriptorRows As Long = 6
Const PerRow As Long = 4
Const RowNext As Long = 3

On Error Resume Next
For count = 20 To 40
high = Ceil(count / PerRow)
sArea = ""
r = -(DescriptorRows - 1) + RowNext
For i = 1 To high
r = r + DescriptorRows
sArea = sArea + ",A" & r & ":B" & r & ",C" & r & ":D" & r & ",E"
& r & ":F" & r & ",G" & r & ":H" & r
Next i
Set rng = Range(Mid$(sArea, 2))
With Err
If .Number < 0 Then
Debug.Print .Number, .Description
Debug.Print vbTab;
.Clear
End If
End With
Debug.Print count, Len(Mid$(sArea, 2)) - 1, Mid$(sArea, 2)
Next count
On Error GoTo 0
End Sub

Public Function Ceil(dblSource As Double) As Long
Dim dblFloor As Double

dblFloor = Int(dblSource)
If dblFloor = dblSource Then
Ceil = dblFloor
Else
Ceil = dblFloor + 1
End If
End Function

FYI, with Excel 2003

For a Range with 35 areas, there are 271 bytes in the string:
This was not allowed.
A4:B4,C4:D4,E4:F4,G4:H4,A10:B10,C10:D10,E10:F10,G1 0:H10,A16:B16,C16:D16,E16:
F16,G16:H16,A22:B22,C22:D22,E22:F22,G22:H22,A28:B2 8,C28:D28,E28:F28,G28:H28,
A34:B34,C34:D34,E34:F34,G34:H34,A40:B40,C40:D40,E4 0:F40,G40:H40,A46:B46,C46:
D46,E46:F46,G46:H46,A52:B52,C52:D52,E52:F52

So, I tried 34 areas with 263 bytes, again, this was not allowed.
A4:B4,C4:D4,E4:F4,G4:H4,A10:B10,C10:D10,E10:F10,G1 0:H10,A16:B16,C16:D16,E16:
F16,G16:H16,A22:B22,C22:D22,E22:F22,G22:H22,A28:B2 8,C28:D28,E28:F28,G28:H28,
A34:B34,C34:D34,E34:F34,G34:H34,A40:B40,C40:D40,E4 0:F40,G40:H40,A46:B46,C46:
D46,E46:F46,G46:H46,A52:B52,C52:D52

So, I tried 33 areas with 255 bytes, this was allowed.
A4:B4,C4:D4,E4:F4,G4:H4,A10:B10,C10:D10,E10:F10,G1 0:H10,A16:B16,C16:D16,E16:
F16,G16:H16,A22:B22,C22:D22,E22:F22,G22:H22,A28:B2 8,C28:D28,E28:F28,G28:H28,
A34:B34,C34:D34,E34:F34,G34:H34,A40:B40,C40:D40,E4 0:F40,G40:H40,A46:B46,C46:
D46,E46:F46,G46:H46,A52:B52

I then finished up with the remaining 2 areas, used 15 bytes.
C52:D52,E52:F52

I looped, using each range object to do the necessary needs.

I need to try a case that has more than 66 areas.

"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
Address limit is 255 in '97 to 2003, up to 16 areas anywhere on the
sheet.

The limit is not 16 areas, I am currently using 25.

I stick by the 255 address limit but you may have misunderstood what I meant
by "up to 16 areas anywhere on the sheet". The key word is "anywhere", also
although not stated each area with two or more cells below and to the right
of AA10000 with an address like "AA12345:AB12345".

If areas are above and to left of AA10000 and/or are single cell areas, more
than 16 areas can be defined within a 255 address.

The absolute max number of areas defineable within the 255 address limit is
85, albeit highly contived

Sub test1()
Dim i As Long, rng As Range

For i = 1 To 85
s = s & Range("A1:J9")(i).Address(0, 0)
If i < 85 Then
s = s & ","
Else: Exit For
End If
Next

Set rng = Range(s)

Debug.Print Len(s), rng.Areas.count ' 254 85

rng.Select

End Sub

Also, the address limit is higher in Excel 97 than in Excel 2003, see code
below.
I ran the code in both 97 and 2003.

The 255 address limit is well documented for all versions (I think also
2007). The 255 limit also applies to some other things such as refersto
strings in names, however the actual limit may be less. For my purposes I
consider an absolute safe llimit at 230.

I have to dynamically create the string, so I can use On Error to see
whether I have to process in chunks.

No, don't brute force like that. Apart from being unnecessarily slow, with
certain combinations the range can be successfully set with an address
truncated shorter than the original 255+ string, down to 255 or less. I
suspect that is what has made you think you can define more areas in XL97
than in 2003. It's a while since I tested but there is a difference in how
this "truncating" can occur between versions.

Keep track of the address length as you go. When it's known that adding one
more area will exceed 255, process the <=255 chunck.

Add the following to your code to see what's really going on -

Debug.Print rng.Areas.count, Len(rng.Address(0, 0))

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I stick by the 255 address limit but you may have misunderstood what I
meant
by "up to 16 areas anywhere on the sheet". The key word is "anywhere",
also
although not stated each area with two or more cells below and to the
right
of AA10000 with an address like "AA12345:AB12345".

The length is not limited to 255 in Excel 97.
Run the example I posted to see this.


If areas are above and to left of AA10000 and/or are single cell areas,
more
than 16 areas can be defined within a 255 address.

The absolute max number of areas defineable within the 255 address limit
is
85, albeit highly contived

I believe that the limit is the string length, not the nimber of areas.

The 255 address limit is well documented for all versions (I think also
2007). The 255 limit also applies to some other things such as refersto
strings in names, however the actual limit may be less. For my purposes I
consider an absolute safe llimit at 230.

The documentation is incorrect.

No, don't brute force like that. Apart from being unnecessarily slow, with
certain combinations the range can be successfully set with an address
truncated shorter than the original 255+ string, down to 255 or less.

It is not slow.
How can one shorten the string?
All areas are two consecutive cells, up to 4 per row, every Nth row.

I
suspect that is what has made you think you can define more areas in XL97
than in 2003. It's a while since I tested but there is a difference in how
this "truncating" can occur between versions.

I stated that the string can be longer.
Run my exanple in Excek 97 to see this.

Keep track of the address length as you go. When it's known that adding
one
more area will exceed 255, process the <=255 chunck.

That does not work because the limit may be different in each version of
Excel.

Debug.Print rng.Areas.count, Len(rng.Address(0, 0))

I've already done that.

"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
I stick by the 255 address limit but you may have misunderstood what I
meant
by "up to 16 areas anywhere on the sheet". The key word is "anywhere",
also
although not stated each area with two or more cells below and to the
right
of AA10000 with an address like "AA12345:AB12345".

The length is not limited to 255 in Excel 97.
Run the example I posted to see this.


If areas are above and to left of AA10000 and/or are single cell areas,
more
than 16 areas can be defined within a 255 address.

The absolute max number of areas defineable within the 255 address limit
is
85, albeit highly contived

I believe that the limit is the string length, not the nimber of areas.

Absolutely, that's what I have been saying all along.

The 255 address limit is well documented for all versions (I think also
2007). The 255 limit also applies to some other things such as refersto
strings in names, however the actual limit may be less. For my purposes
I
consider an absolute safe llimit at 230.

The documentation is incorrect.

No, don't brute force like that. Apart from being unnecessarily slow,
with
certain combinations the range can be successfully set with an address
truncated shorter than the original 255+ string, down to 255 or less.

It is not slow.
How can one shorten the string?
All areas are two consecutive cells, up to 4 per row, every Nth row.

Keep track of the how much you are increasing the address by in each loop.
If len(address) + len(to be added next next loop) 255 then do that chunk.
Might need to subtract a comma.


I
suspect that is what has made you think you can define more areas in
XL97
than in 2003. It's a while since I tested but there is a difference in
how
this "truncating" can occur between versions.

I stated that the string can be longer.
Run my exanple in Excek 97 to see this.

Keep track of the address length as you go. When it's known that adding
one
more area will exceed 255, process the <=255 chunck.

That does not work because the limit may be different in each version of
Excel.

Debug.Print rng.Areas.count, Len(rng.Address(0, 0))

I've already done that.


I should have fully tested in Excel 97, apologies for not having done so.
Indeed it does appear possible to make a range with an address up to about
300. However it is not possible to return the address directly, or rather
the return address is truncated to 255.

I wonder if it's not a bug in XL 97, at the very least an anomaly.

Sub test()
Dim s As String, i As Long, k As Long, dw As Long
Dim rng As Range

For dw = 240 To 400 Step 10
i = 0: k = 0: s = ""
Do While Len(s) < dw
i = i + 2: k = k + 1
s = s & Cells(i, 1).Address(0, 0) & ","
Loop

s = Left(s, Len(s) - 1)
Set rng = Range(s)
' no point to return the address, it'll truncate at 255

MsgBox k & " " & rng.Areas.Count, , Len(s)

Next
End Sub

At around the next loop after 310-320 attempting to assign the long address
crashes my xl97, or it might have been the attempt to read .areas.count,
difficult to tell. Either way it points more to a bug than an anomaly.

Despite the apparent ability to be able set a range with an address of 255+
to 300 or even a bit more in xl97 I really would consider 255 as the limit
for all versions.

Regards,
Peter T

String concatenation is very slow.

It's faster to build a list of the addresses, then use Join to create the
string.
Then I test the range, if it doesn't work, I shorten the string, until it
works.
Then I process the rest of the string in a similar manner.
This method is version independent, and is not slow.

I will not process based on a string length as it is clear that Excel 97
allows more than the documented 255.

My 1st attempt at throwing something together was not bad since it was pre
OJ today.
I'll improve it later.

sArea = Replace(Join(sAreas, ","), "$", "")
last = Len(sArea)
lenDone = 0
sTemp = sArea
iComma = 0
On Error Resume Next
Do While lenDone < last
Do
Err.Clear
' Debug.Print Len(sTemp), sTemp
Set rng = .Application.Range(sTemp)
If Err.Number = 0 Then
' Debug.Print "Yippee!!!!!"
lenDone = lenDone + Len(sTemp) + iComma
rng.MergeCells = vbTrue
If lenDone = last Then
Exit Do
Else
sTemp = Mid$(sArea, lenDone + 1)
iComma = 1
End If
Else
i = InStrRev(sTemp, ",")
sTemp = Left$(sTemp, i - 1)
iComma = 1
End If
Loop
Loop
On Error GoTo 0

I added timing to the inner guts of the program
Witrh Screenupdating disabled, time was 3943 milliseconds.
With Screenupdating enabled, time was 4833 milliseconds, that's one heck of
a
relative performance hit!

Both of the above were run from the VBIDE.

I compiled the VB 6 code, time to run was 3924 millisconds.

Excel is not visible in all cases.

Perhaps a silly question, but is there "documentation" of how a disabled
Screenupdating affects non-visible documents?

Oh well, a few daze ago the code was taking over 50 seconds.

"Howard Kaikow" wrote in message
I added timing to the inner guts of the program
Witrh Screenupdating disabled, time was 3943 milliseconds.
With Screenupdating enabled, time was 4833 milliseconds, that's one heck
of
a
relative performance hit!

I posted a couple of demos a while back that showed, respectively,
significant and trivial speed gain by disabling screenupdating (SU)
depending on what the code does to cells. As I said at the time I would not
have expected much gain with what you are doing, however difiicult to
imagine why disabling SU affects your code so negatively.

Couple of thoughts: Are you toggling SU on/off multiple times in a loop (I
assume not). Is there anything that would take a long time on the sheet to
redraw, eg graphics, page breaks etc.

Both of the above were run from the VBIDE.

I compiled the VB 6 code, time to run was 3924 millisconds.

Excel is not visible in all cases.

With Excel is visible it was very worthwhile to disable SU even running the
demo that gave small gain in an invisible instance.

Perhaps a silly question, but is there "documentation" of how a disabled
Screenupdating affects non-visible documents?

Not that I know of.

Oh well, a few daze ago the code was taking over 50 seconds.

Keep chipping away!

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I posted a couple of demos a while back that showed, respectively,
significant and trivial speed gain by disabling screenupdating (SU)
depending on what the code does to cells. As I said at the time I would
not
have expected much gain with what you are doing, however difiicult to
imagine why disabling SU affects your code so negatively.

Couple of thoughts: Are you toggling SU on/off multiple times in a loop (I
assume not). Is there anything that would take a long time on the sheet to
redraw, eg graphics, page breaks etc.

Excel is NOT visible.

I expect that the RowHeight, ColumnWidth , and AutoFit changes are causing a
repaint internally.
Poor implementation of ScreenUpdating.

Not that I know of.

"Microsoft documentation" is an oxymoron, to be polite.