VBA Hide rows is too slow

[ToniGo]

Hi all, I am hoping someone can help me here...I have a fairly large spreadsheet (~12MB) which I am setting up a navigation tool to "scroll" between sections. This is done by a drop down and is linked to various other parameters and all works fine. However the issue is that it is quite slow and given that it is supposed to make navigation easier I would LIKE if it could be fairly instant. The slow part is the hiding rows (which I tried to get around by setting row height to 0.5 rather than 0 but this didn't seem to work.) The rows must be hidden so I can freeze panes at that section. If I turn calculations off then the hiding rows is fine BUT turning calculations back to auto takes ages as the whole thing recalculates (even though nothing has happened apart from the row heights) so either way it doesn't seem to make any difference to the overall speed. Any ideas?? My code is below.

Sub GotoItem()
Dim rownum, numlines, numitems, acrange, seclines As Integer

If Range("itemchose").Value = "" Then Exit Sub
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
ActiveWindow.View = xlNormalView
ActiveSheet.DisplayPageBreaks = False
acrange = ActiveWindow.ScrollColumn
ActiveWindow.FreezePanes = False
Cells(10, 12).Select
ActiveWindow.FreezePanes = True
numlines = Range("numlines").Value
numitems = Range("numitems").Value
seclines = Range("seclines").Value
rownum = 10 + (Range("itemlast").Value - 1) * Range("seclines").Value 'hides last viewed section
Rows(rownum & ":" & rownum + seclines - 1).EntireRow.RowHeight = 0.5
rownum = 10 + (Range("itemchose").Value - 1) * Range("seclines").Value 'autofits selected selection
Rows(rownum & ":" & rownum + seclines - 1).EntireRow.AutoFit

ActiveWindow.ScrollRow = rownum
Cells(rownum + Range("numlines").Value + 1, 12).Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
Range("itemlast").Value = Range("itemchose").Value
ActiveWindow.ScrollColumn = acrange
With Application
    .ScreenUpdating = True
    .EnableEvents = True
   .Calculation = xlCalculationAutomatic
End With
End Sub

 

[ToniGo]

Also further to my post, I meant to say that there doesn't appear to be the same lag if I do the same thing on the actual worksheet - even if calculation is set to auto, it is basically instant if select all the rows and set the row height.