Cannot scroll to bottom of Scroll Area.

[smiley3141]

I have a sheet that has data in the range A1:K1077. I set the scroll area in VBA to "A1:K1078". This macro runs each time I activate the tab. The first time it runs, I can see the whole scroll area. After that it cuts me off and I cannot scroll down and see the last 7 or 8 lines. I have increased the scroll area all the way up to row 1300, and this does not resolve the problem. Does anyone know what the problem might be and a possible solution?

I have thought of hiding all the rows below 1078, but when I set the scroll area to the first 1078 rows and hid the bottom, it allowed me to scroll past the bottom and see a blank gray area. I do not want that.

It is getting late here, so if you respond it might take a while for me to get back to you.

Thanks for any help you can offer me.

 

[Andrew Poulsom]

What macro?

 

[smiley3141]

Private Sub Worksheet_Activate()

Dim r As Range
PS_Reset
With ActiveSheet
For Each r In Range("A10:A1053")
If (r.Value = "Clear Formula") Then
r.Offset(0, 4).ClearContents
End If
Next
End With

Rows("9:9").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("9:1050").EntireRow.AutoFit
Range("PS_RowsToHide").Select
Selection.EntireRow.Hidden = True


With Sheets("Property Settlement")
.ScrollArea = "A1:K1078"
' .Range("G1077").Resize(Rows.Count - 1077, 1).EntireRow.Hidden = True
End With

Range("A1").Select

End Sub

 

[Andrew Poulsom]

What's PS_Reset?

 

[smiley3141]

Sub PS_Reset()
'
' Unhides all rows, then copies down the Description formula _
to all rows below.
'
' Worksheets("Property Settlement").ScrollArea = ""
Range("PS_Unhide").Select
Selection.EntireRow.Hidden = False
Range("PS_DescriptionTop").Select
Selection.Copy
Range("PS_CopyDown").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

 

[Andrew Poulsom]

What do the ranges "PS_Unhide", "PS_DescriptionTop", "PS_CopyDown" and "PS_RowsToHide" refer to?

 

[smiley3141]

PS_Unhide is rows that were hidden previously, but are unhidden since new data may have been entered since the last time the sheet was activated. It is currently 833:1051. "PS_descriptionTop" is Cell E10. The formula in this cell is copied (formula only) into E11:E1051 (which is "PS_CopyDown"). PS_RowsToHide is the rows that have no data, at the time the sheet is activated, so they are hidden.

 

[Andrew Poulsom]

What does the name PS_RowsToHide currently refer to?

 

[parara]

I'm running into the same problem.

It should be possible to reproduce this:

1. Enter enough values into column A so that you'll have to use the vertical scrollbar in order to view all cells that contain data (e.g. from A1-A100).
2. Now set ScrollArea accordingly (e.g. A1-A100).
3. Scroll to the top of the sheet.
4. Now use the mousewheel (don't drag the scrollbar or click the scrollbar arrows!) to scroll all the way down to the last cell in the ScrollArea (e.g. A100).
5. If you're lucky, the last cell of the ScrollArea is still visible, if you're not, it's cut off (because you couldn't use the mousewheel to scroll all the way to the bottom - look at the position of the scrollbar, it didn't fully reach the bottom!)
6. In case the last cell was still visible, add a value to the next cell (e.g. A101) and adjust the ScrollArea (e.g. A1-A101). Start again from step 3.
7. If there weren't any cut off cells, add another cell, etc.

Let me know if anything is unclear.

Thanks!

 

[parara]

Found some old threads with the same problem - and an explanation:

• http://www.mrexcel.com/forum/excel-questions/40442-scroll-area.html
• http://www.mrexcel.com/forum/excel-questions/55538-scroll-area.html

Is there any way around this though?