Excel VBA bug?
 

Hello all,

I observed that after I run the following "print" macro (with some rows
hidden):
________________
Sub Print()
Range("A1").Select
Selection.CurrentRegion.Select
With ActiveSheet.PageSetup
.PrintHeadings = True
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Selection.PrintOut Copies:=1, Collate:=True
Range("A1").Select
End Sub
________________

....the macro below is executed very slowly (takes 20 seconds instead of 1
second normally)
________________
Sub RowsHide()

Application.ScreenUpdating = False

Dim iRow As Long
Dim maxRows As Long

maxRows = Range("$A$1").CurrentRegion.Rows.Count
' maxRows2 = ActiveSheet.UsedRange.Rows.Count
With Worksheets(ActiveSheet.Name)
For iRow = 1 To maxRows
If (.Cells(iRow, 1).Interior.ColorIndex = 6 Or _
.Cells(iRow, 1).Value = "") And _
(.Cells(iRow, 2).Interior.ColorIndex = 6 Or _
.Cells(iRow, 2).Value = "") Then
.Rows(iRow).Hidden = True
End If
Next iRow
End With

Application.ScreenUpdating = True

End Sub
________________

but as long as the "print" macro is non executed, the macro "rows hide" is
running OK (env. 1 second needed to get the results).

Any idea why it's like this and how to correct this issue? (I can always
close the workbook and re-open in again, but it's not the optimal solution
of course).

Other macros are executed with no additional delay, so the problem really
seems to be somewhere in the interaction between these both codes.

Thanks for your input,
Mark

Excel VBA bug?
 

Few things to try

In your first macro you must add

.Zoom = False
for printing on one page


See this page for a few things you can change in the loop
http://www.rondebruin.nl/delete.htm
you can change the delete for code for hide

See viewmode, displaypagebreaks...


--
Regards Ron de Bruin
http://www.rondebruin.nl



"markx" wrote in message ...
Hello all,

I observed that after I run the following "print" macro (with some rows hidden):
________________
Sub Print()
Range("A1").Select
Selection.CurrentRegion.Select
With ActiveSheet.PageSetup
.PrintHeadings = True
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Selection.PrintOut Copies:=1, Collate:=True
Range("A1").Select
End Sub
________________

...the macro below is executed very slowly (takes 20 seconds instead of 1 second normally)
________________
Sub RowsHide()

Application.ScreenUpdating = False

Dim iRow As Long
Dim maxRows As Long

maxRows = Range("$A$1").CurrentRegion.Rows.Count
' maxRows2 = ActiveSheet.UsedRange.Rows.Count
With Worksheets(ActiveSheet.Name)
For iRow = 1 To maxRows
If (.Cells(iRow, 1).Interior.ColorIndex = 6 Or _
.Cells(iRow, 1).Value = "") And _
(.Cells(iRow, 2).Interior.ColorIndex = 6 Or _
.Cells(iRow, 2).Value = "") Then
.Rows(iRow).Hidden = True
End If
Next iRow
End With

Application.ScreenUpdating = True

End Sub
________________

but as long as the "print" macro is non executed, the macro "rows hide" is running OK (env. 1 second needed to get the results).

Any idea why it's like this and how to correct this issue? (I can always close the workbook and re-open in again, but it's not the
optimal solution of course).

Other macros are executed with no additional delay, so the problem really seems to be somewhere in the interaction between these
both codes.

Thanks for your input,
Mark