Row Height -- Slows VBA

[doodles82a]

Row heights are static and defined in Cells (FA1:FA100). An hyperlink
event hides Rows (1:90), thereby making their row height equal to
zero. A second hyperlink event unhides Rows (1:90). VBA code loops
through the values in Cells (FA1:F100) to define the appropriate row
height for each row.

This works fine. It works quickly, instantaneously.

Then, however, I'll do either a print or a print preview on the
worksheet. The print and print preview work fine.

Subsequently, however, the time it takes for the code associated with
either hyperlink noticeably increases. Something associated with the
print or print preview alters the behavior of the code that re-
establishes the row height for each row.

I trapped the code and manually processed through the code. With each
loop, one through one hundred, the row height for each row set
properly, but with a brief hour glass display on each pass. Code that
once passed through each row height setting instantaneously now took a
fraction of a second. The cumulative effect of this, over one hundred
row height settings, is quite significant, perhaps fifteen seconds.

The screen update and the enable events are set to false as the code
passes through the loop. The calculation is set to xlManual. The print
setting crams one hundred rows of information into one page. However,
the same problems exist when I remove the "fit to one page" parameter.

Have any of you encountered this? I'm using Excel 2003.

Thank you for your time and consideration.

Michael

 

[Barb Reinhardt]

Why don't you do something like this

Sub Test()
Dim myRange As Range
Set myRange = Rows("1:10")

myRange.EntireRow.Hidden = True

End Sub

instead of doing it row by row.

 

[Dave Peterson]

(Saved from a previous post)

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.

 

[Rick Rothstein]

You don't show us any code (always a good idea to include your code) so we
can't see what you are doing, but you can hide/unhide rows all at once
without having to reset the row heights individually. This single line...

Rows("1:90").Hidden = True

will hide rows 1 through 90 and this line...

Rows("1:10").Hidden = False

will unhide them and each row will have the same height it had before it was
hidden.

 

[Rick Rothstein]

Of course, the statement to unhide the 90 rows should have been...

Rows("1:90").Hidden = False

 

[doodles82a]

(Saved from a previous post)

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

You may want to do something like:

Option Explicit
Sub testme()

    Dim CalcMode As Long
    Dim ViewMode As Long

    Application.ScreenUpdating = False

    CalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual

    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView

    ActiveSheet.DisplayPageBreaks = False

    'do the work

    'put things back to what they were
    Application.Calculation = CalcMode
    ActiveWindow.View = ViewMode

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave Peterson, nice job you mensch??? Thanks so much. Your diagnosis
was correct and your suggestion worked!

To everyone else, thank you for your time and effort. I failed to
mention that the size of each row was not uniform. Therefore, the
values from (FA1:F100) needed to be read, individually, to set each
row to an appropriate height. A loop was the only way I could perform
this tasks. HAPPY HOLIDAYS!

 

[Rick Rothstein]

To everyone else, thank you for your time and effort.

I failed to mention that the size of each row was not
uniform. Therefore, the values from (FA1:F100)
needed to be read, individually, to set each row
to an appropriate height. A loop was the only way
I could perform this tasks.

Unless I am misunderstanding your requirements, did you read my post
*carefully*?

 

[doodles82a]

Unless I am misunderstanding your requirements, did you read my post
*carefully*?

Prior to executing the code that first hid all the rows, some of the
rows had already been hidden, as a default. The desired value of the
row height for those default hidden rows is zero.

The first set of code hides all rows between row 1 and row 90. This
includes certain rows between 1 and 90 that had been hidden as a
default.

The second set of code unhides all rows between row 1 and row 90.

After the second set of code is executed, most rows between 1 and 90
expand to their previous row height, as you stated. The rows that were
hidden, as a default prior to the execution of the first set of code,
unhide and expand to a certain height. The goal for these rows is to
set the height at zero, though. That's why I used code to loop through
all rows. Since the process is automated, my users can play with the
row heights of each row if they wish to change the size of the default
for certain rows. Consequently, they won't need to involve me in those
decisions.

Thanks for your time. I wish I had been more clear in stating my
problem.