RowHeight and AutoFit wit Merged Cells

[Peter T]

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.

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.

 

[Peter T]

Sorry about the double post.

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

 

[Howard Kaikow]

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

 

[Howard Kaikow]

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.

 

[Howard Kaikow]

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

 

[Peter T]

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

 

[Howard Kaikow]

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 & "" & 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

 

[Howard Kaikow]

FYI, with Excel 2003

For a Range with 35 areas, there are 271 bytes in the string:
This was not allowed.
A4:B4,C44,E4:F4,G4:H4,A10:B10,C1010,E10:F10,G10:H10,A16:B16,C1616,E16:
F16,G16:H16,A22:B22,C2222,E22:F22,G22:H22,A28:B28,C2828,E28:F28,G28:H28,
A34:B34,C3434,E34:F34,G34:H34,A40:B40,C4040,E40:F40,G40:H40,A46:B46,C46:
D46,E46:F46,G46:H46,A52:B52,C5252,E52:F52

So, I tried 34 areas with 263 bytes, again, this was not allowed.
A4:B4,C44,E4:F4,G4:H4,A10:B10,C1010,E10:F10,G10:H10,A16:B16,C1616,E16:
F16,G16:H16,A22:B22,C2222,E22:F22,G22:H22,A28:B28,C2828,E28:F28,G28:H28,
A34:B34,C3434,E34:F34,G34:H34,A40:B40,C4040,E40:F40,G40:H40,A46:B46,C46:
D46,E46:F46,G46:H46,A52:B52,C5252

So, I tried 33 areas with 255 bytes, this was allowed.
A4:B4,C44,E4:F4,G4:H4,A10:B10,C1010,E10:F10,G10:H10,A16:B16,C1616,E16:
F16,G16:H16,A22:B22,C2222,E22:F22,G22:H22,A28:B28,C2828,E28:F28,G28:H28,
A34:B34,C3434,E34:F34,G34:H34,A40:B40,C4040,E40: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.
C5252,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.

 

[Peter T]

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

 

[Howard Kaikow]

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.

 

[Peter T]

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



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 documentation is incorrect.


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 stated that the string can be longer.
Run my exanple in Excek 97 to see this.


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


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

 

[Howard Kaikow]

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

 

[Howard Kaikow]

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.

 

[Peter T]

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

 

[Howard Kaikow]

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.