Speed up slow autofit and hide row vba

[Excel-lecxE]

Hi,

We've recently updated our computers and as such moved from Excel 2000 to Excel 2010. It became immediately obvious that some of the macros which ran almost instantaneously in Excel 2000 have now become sluggish in Excel 2010. One of these macros is used to hide a row in a given range dependant on the value ("True" or "False") given in a helper column as well as resizing and auto fitting the rows in this range. Code below.

Sub Autofit_HideRows()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim rRow As Range
With Range("B10:B391").EntireRow
.Autofit
For Each rRow In .Rows
If rRow.RowHeight < 30 Then rRow.RowHeight = 30
Next rRow
End With

Range("A10:A379").Select
For Each Cell In Selection
If Cell = False Then
Range(Cell.Address).EntireRow.Hidden = True
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

I've tried searching for ways to speed up the process with no luck so here I'm asking for advice.

Thanks in advance.

[Red Spot]

Try this;

Please Login or Register  to view this content.

[Excel-lecxE]

Hi Red Spot,

Thanks for the code, which looks a lot cleaner and ordered than mine. But is unfortunately slower. I don't understand.

I know it might not sound much but in Excel 2000 the code ran almost unnoticed as the user clicked the button the sheet reacted without to much of a noticeable pause but now it hangs for about 12 seconds.

I'm asking to much of Excel 2010.

Thanks

[Red Spot]

You can apply an autofilter and use that to remove the lines that have False in Column A, but I doubt it will solve it for you.
Sounds more like you have a shortage of RAM in your PC.

At a previous employer they used Vista with 1GB RAM and exstensive use of Access, everything went at a crawl, untill they put an other GB of RAM in our PC's.

[AB33]

Does this help?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

In to variable loop, using the last row

Please Login or Register  to view this content.