Excel 2000 vs 2003

[Simon Shaw]

Hi,

the following code runs very quickly in Excel 2000, but very slowly in Excel
2003:

With ActiveSheet
For x = 1 To .Cells.SpecialCells(xlLastCell)
With .Cells(x, 1)
With .EntireRow
If .Hidden = False Then
.AutoFit
End If
End With
End With
Next x
End With

ideas?

Thanks

Simon

[Simon Shaw]

Code line:
For x = 1 To .Cells.SpecialCells(xlLastCell)
is actually:
For x = 1 To .Cells.SpecialCells(xlLastCell).Row


"Simon Shaw" wrote:

> Hi,
>
> the following code runs very quickly in Excel 2000, but very slowly in Excel
> 2003:
>
> With ActiveSheet
> For x = 1 To .Cells.SpecialCells(xlLastCell)
> With .Cells(x, 1)
> With .EntireRow
> If .Hidden = False Then
> .AutoFit
> End If
> End With
> End With
> Next x
> End With
>
> ideas?
>
> Thanks
>
> Simon

[Dave Peterson]

Sometimes, it's not the version of excel, but the fact that you're unhiding
rows.

Maybe adding:
ActiveSheet.DisplayPageBreaks = False
would help.

But xl2003 also changed the way it calculates (manually hidden rows in an
autofiltered range can be excluded from the =subtotal() function).

Maybe turning off calculation would help:

Option Explicit
Sub yoursub()

Dim CalcMode As Long
CalcMode = Application.Calculation
ActiveSheet.DisplayPageBreaks = False

'your code

Application.Calculation = CalcMode

End Sub

Simon Shaw wrote:
>
> Hi,
>
> the following code runs very quickly in Excel 2000, but very slowly in Excel
> 2003:
>
> With ActiveSheet
> For x = 1 To .Cells.SpecialCells(xlLastCell)
> With .Cells(x, 1)
> With .EntireRow
> If .Hidden = False Then
> .AutoFit
> End If
> End With
> End With
> Next x
> End With
>
> ideas?
>
> Thanks
>
> Simon

--

Dave Peterson

[[email protected]]

Even adding the suggested lines the macro is much more fast in Excel
2000.

Regards
Hamilton R. Romano

Dave Peterson wrote:
> Sometimes, it's not the version of excel, but the fact that you're
unhiding
> rows.
>
> Maybe adding:
> ActiveSheet.DisplayPageBreaks = False
> would help.
>
> But xl2003 also changed the way it calculates (manually hidden rows
in an
> autofiltered range can be excluded from the =subtotal() function).
>
> Maybe turning off calculation would help:
>
> Option Explicit
> Sub yoursub()
>
> Dim CalcMode As Long
> CalcMode = Application.Calculation
> ActiveSheet.DisplayPageBreaks = False
>
> 'your code
>
> Application.Calculation = CalcMode
>
> End Sub
>
> Simon Shaw wrote:
> >
> > Hi,
> >
> > the following code runs very quickly in Excel 2000, but very slowly
in Excel
> > 2003:
> >
> > With ActiveSheet
> > For x = 1 To .Cells.SpecialCells(xlLastCell)
> > With .Cells(x, 1)
> > With .EntireRow
> > If .Hidden = False Then
> > .AutoFit
> > End If
> > End With
> > End With
> > Next x
> > End With
> >
> > ideas?
> >
> > Thanks
> >
> > Simon
>
> --
>
> Dave Peterson