The below macro, which I have left literally as coded, runs very quickly
from Excel -- until, in Excel, I do either a print or print preview in
native Excel, not VBA. After which, the macro, when next invoked, runs
about 10 times slower which makes it performance unacceptable. If I close
and reopen the spreadsheet, however, it seems to reset the internal
environment and the macro once again runs nicely. Until the next print or
print preview.

The macro is intended to display only selected portions of a large table
spanning about 12000 rows. The print range, however, is set to run from the
top of the table to just one past the end of the table, or row 12001, in
order to include all lines currently unhidden. Excel takes a little time
working through this formatting before displaying. And I wonder if this is
leaving Excel slightly corrupted. I don't see how the issue could be the
macro, but I have included below for reference.

Version is Excel 2002. Any thoughts? Thanks in advance. Larry.

Private Sub cmdPlusOne_Click()
Dim i, curr_level, row As Integer, c As String
Worksheets("FOCUS").Activate
c = ActiveCell.Address
row = ActiveCell.row
With Range("A" & row)
curr_level = .Offset(0, 0)
For i = 1 To 20000
If (.Offset(i, 0) = Null) Or (.Offset(i, 0) <= curr_level) Then
Exit For
ElseIf .Offset(i, 0) = curr_level + 1 Then
Rows("" & (row + i) & ":" & (row + i) & "").Select
Selection.EntireRow.Hidden = False
End If
Next i
Range(c).Select
End With
End Sub


(Just as further FYI, the table being processed by the macro above is
actually an outline. Level refers to the outline indent level of the current
active cell. The macro is intended to then unhide any entries one level
further down in the outline.)

Larry,

Try adding...

Activesheet.DisplayPageBreaks = False

just before... Next i

Regards,
Jim Cone
San Francisco, USA



"Larry A" wrote in message
. ..
The below macro, which I have left literally as coded, runs very quickly
from Excel -- until, in Excel, I do either a print or print preview in
native Excel, not VBA. After which, the macro, when next invoked, runs
about 10 times slower which makes it performance unacceptable. If I close
and reopen the spreadsheet, however, it seems to reset the internal
environment and the macro once again runs nicely. Until the next print or
print preview.

The macro is intended to display only selected portions of a large table
spanning about 12000 rows. The print range, however, is set to run from the
top of the table to just one past the end of the table, or row 12001, in
order to include all lines currently unhidden. Excel takes a little time
working through this formatting before displaying. And I wonder if this is
leaving Excel slightly corrupted. I don't see how the issue could be the
macro, but I have included below for reference.
Version is Excel 2002. Any thoughts? Thanks in advance. Larry.

Private Sub cmdPlusOne_Click()
Dim i, curr_level, row As Integer, c As String
Worksheets("FOCUS").Activate
c = ActiveCell.Address
row = ActiveCell.row
With Range("A" & row)
curr_level = .Offset(0, 0)
For i = 1 To 20000
If (.Offset(i, 0) = Null) Or (.Offset(i, 0) <= curr_level) Then
Exit For
ElseIf .Offset(i, 0) = curr_level + 1 Then
Rows("" & (row + i) & ":" & (row + i) & "").Select
Selection.EntireRow.Hidden = False
End If
Next i
Range(c).Select
End With
End Sub
(Just as further FYI, the table being processed by the macro above is
actually an outline. Level refers to the outline indent level of the current
active cell. The macro is intended to then unhide any entries one level
further down in the outline.)

As for the initial question, WHY.
Because each time you insert/delete/hide/unhide a row or
column Excel must recalculate where the page breaks are.

So you really had the answer when you asked, and Jim
provided you with the code.

You macro is not very efficient, If I am reading what it does
correctly the following would do same.

Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Hidden = True

Unless you reduce the macro to a single line as above, I would
suggest that you also turn off screen display and calculation during
the execution of your macro.
http://www.mvps.org/dmcritchie/excel...htm#slowmacros
http://www.mvps.org/dmcritchie/excel...esp.htm#insert

Also I can't help but wonder if you are simply trying to hide
lastcell problems.
http://www.mvps.org/dmcritchie/excel...m#makelastcell

But what you probably want to be using is a filter (data, Filter, Auto)
after selecting column A, then on the filter drop down in cell A1
choose nonblanks. More information on filters
http://www.contextures.com/tiptech.html look for "data filters"

For the macro to hide and unhide, since it is one line of code
I don't think it will actually make any difference if you turn
screen updating and calculation off, nor even do anything
about page break display. Let me know if you can actually
see the difference.

Sub Macro1()
' ActiveSheet.DisplayPageBreaks = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Hidden = True
End Sub
Sub Macro2()
' ActiveSheet.DisplayPageBreaks = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Hidden = False
' ActiveSheet.DisplayPageBreaks = True
End Sub

Something with Autofilter:

Sub Macro3()
Columns("A:A").AutoFilter Field:=1, Criteria1:="<"
End Sub
Sub Macro4()
Columns("A:A").AutoFilter
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim Cone" wrote in message ...
Larry,

Try adding...

Activesheet.DisplayPageBreaks = False

just before... Next i

Regards,
Jim Cone
San Francisco, USA



"Larry A" wrote in message
. ..
The below macro, which I have left literally as coded, runs very quickly
from Excel -- until, in Excel, I do either a print or print preview in
native Excel, not VBA. After which, the macro, when next invoked, runs
about 10 times slower which makes it performance unacceptable. If I close
and reopen the spreadsheet, however, it seems to reset the internal
environment and the macro once again runs nicely. Until the next print or
print preview.

The macro is intended to display only selected portions of a large table
spanning about 12000 rows. The print range, however, is set to run from the
top of the table to just one past the end of the table, or row 12001, in
order to include all lines currently unhidden. Excel takes a little time
working through this formatting before displaying. And I wonder if this is
leaving Excel slightly corrupted. I don't see how the issue could be the
macro, but I have included below for reference.
Version is Excel 2002. Any thoughts? Thanks in advance. Larry.

Private Sub cmdPlusOne_Click()
Dim i, curr_level, row As Integer, c As String
Worksheets("FOCUS").Activate
c = ActiveCell.Address
row = ActiveCell.row
With Range("A" & row)
curr_level = .Offset(0, 0)
For i = 1 To 20000
If (.Offset(i, 0) = Null) Or (.Offset(i, 0) <= curr_level) Then
Exit For
ElseIf .Offset(i, 0) = curr_level + 1 Then
Rows("" & (row + i) & ":" & (row + i) & "").Select
Selection.EntireRow.Hidden = False
End If
Next i
Range(c).Select
End With
End Sub
(Just as further FYI, the table being processed by the macro above is
actually an outline. Level refers to the outline indent level of the current
active cell. The macro is intended to then unhide any entries one level
further down in the outline.)

That did the trick, Jim. Dave, just to explain the app a little further,
column A has a calculated indent level for each row in the outline. The
outline all resides in the single column B -- with each outline entry having
five leading spaces for each indent level. Which is why I inspect rows
individually. However, you've given me some new things to look as well,
which I appreciate. I considered turning off the screen display, but the
macro runs fast enough (now) and I think my users will find the active
movement on screen more interesting. And there's not that much calcing
involved.

THANKS MUCH!!! Larry.

"David McRitchie" wrote in message
...
As for the initial question, WHY.
Because each time you insert/delete/hide/unhide a row or
column Excel must recalculate where the page breaks are.

So you really had the answer when you asked, and Jim
provided you with the code.

You macro is not very efficient, If I am reading what it does
correctly the following would do same.

Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Hidden = True

Unless you reduce the macro to a single line as above, I would
suggest that you also turn off screen display and calculation during
the execution of your macro.
http://www.mvps.org/dmcritchie/excel...htm#slowmacros
http://www.mvps.org/dmcritchie/excel...esp.htm#insert

Also I can't help but wonder if you are simply trying to hide
lastcell problems.
http://www.mvps.org/dmcritchie/excel...m#makelastcell

But what you probably want to be using is a filter (data, Filter, Auto)
after selecting column A, then on the filter drop down in cell A1
choose nonblanks. More information on filters
http://www.contextures.com/tiptech.html look for "data filters"

For the macro to hide and unhide, since it is one line of code
I don't think it will actually make any difference if you turn
screen updating and calculation off, nor even do anything
about page break display. Let me know if you can actually
see the difference.

Sub Macro1()
' ActiveSheet.DisplayPageBreaks = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Hidden = True
End Sub
Sub Macro2()
' ActiveSheet.DisplayPageBreaks = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Hidden = False
' ActiveSheet.DisplayPageBreaks = True
End Sub

Something with Autofilter:

Sub Macro3()
Columns("A:A").AutoFilter Field:=1, Criteria1:="<"
End Sub
Sub Macro4()
Columns("A:A").AutoFilter
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim Cone" wrote in message
...
Larry,

Try adding...

Activesheet.DisplayPageBreaks = False

just before... Next i

Regards,
Jim Cone
San Francisco, USA



"Larry A" wrote in message
. ..
The below macro, which I have left literally as coded, runs very
quickly
from Excel -- until, in Excel, I do either a print or print preview in
native Excel, not VBA. After which, the macro, when next invoked, runs
about 10 times slower which makes it performance unacceptable. If I
close
and reopen the spreadsheet, however, it seems to reset the internal
environment and the macro once again runs nicely. Until the next print
or
print preview.

The macro is intended to display only selected portions of a large
table
spanning about 12000 rows. The print range, however, is set to run
from the
top of the table to just one past the end of the table, or row 12001,
in
order to include all lines currently unhidden. Excel takes a little
time
working through this formatting before displaying. And I wonder if
this is
leaving Excel slightly corrupted. I don't see how the issue could be
the
macro, but I have included below for reference.
Version is Excel 2002. Any thoughts? Thanks in advance. Larry.

Private Sub cmdPlusOne_Click()
Dim i, curr_level, row As Integer, c As String
Worksheets("FOCUS").Activate
c = ActiveCell.Address
row = ActiveCell.row
With Range("A" & row)
curr_level = .Offset(0, 0)
For i = 1 To 20000
If (.Offset(i, 0) = Null) Or (.Offset(i, 0) <= curr_level)
Then
Exit For
ElseIf .Offset(i, 0) = curr_level + 1 Then
Rows("" & (row + i) & ":" & (row + i) & "").Select
Selection.EntireRow.Hidden = False
End If
Next i
Range(c).Select
End With
End Sub
(Just as further FYI, the table being processed by the macro above is
actually an outline. Level refers to the outline indent level of the
current
active cell. The macro is intended to then unhide any entries one
level
further down in the outline.)

Hi Larry,
Thanks for the reply, I guess I'll just leave it as a macro that
doesn't do anything like what I thought it was for.

"Larry A" wrote in message ...
That did the trick, Jim. Dave, just to explain the app a little further,
column A has a calculated indent level for each row in the outline. [clipped]

Larry,

A couple of generic comments...

"Row" is a property of the Range object.
It should not be used as a variable.

A numeric variable referring to a row number should
be a Long not an Integer.

Regards,
Jim Cone
San Francisco, USA



"Larry A" wrote in message
...
That did the trick, Jim. Dave, just to explain the app a little further,
column A has a calculated indent level for each row in the outline. The
outline all resides in the single column B -- with each outline entry having
five leading spaces for each indent level. Which is why I inspect rows
individually. However, you've given me some new things to look as well,
which I appreciate. I considered turning off the screen display, but the
macro runs fast enough (now) and I think my users will find the active
movement on screen more interesting. And there's not that much calcing
involved.
THANKS MUCH!!! Larry.

-snip-

Jim, good points. And thanks again for the help!! This app can now go out
to my users this morning. Larry.

"Jim Cone" wrote in message
...
Larry,

A couple of generic comments...

"Row" is a property of the Range object.
It should not be used as a variable.

A numeric variable referring to a row number should
be a Long not an Integer.

Regards,
Jim Cone
San Francisco, USA



"Larry A" wrote in message
...
That did the trick, Jim.

-snip-