SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
Hi, I'm building a navigation screen for users to get down a long worksheet.
Progress so far:
1. User selects a heading from a drop-down list and hits "Go" button.
2. Code builds an array of all cells in Column A used range with a yellow background (this is in case user adds/deletes rows) -- row numbers added to array.
3. Cell A10 has a calc showing which item in the list was selected
4. Cell (Array item #,1) is selected.
So far, so good. Trouble is, when the cell is selected it's vertically centred in the scrollable window (I have Window | Freeze panes on).
Question: WITHOUT hiding rows, how do I position the selected cell at the top of the scrollable section?
Any help appreciated -- code is shown below.
Denis
Progress so far:
1. User selects a heading from a drop-down list and hits "Go" button.
2. Code builds an array of all cells in Column A used range with a yellow background (this is in case user adds/deletes rows) -- row numbers added to array.
3. Cell A10 has a calc showing which item in the list was selected
4. Cell (Array item #,1) is selected.
So far, so good. Trouble is, when the cell is selected it's vertically centred in the scrollable window (I have Window | Freeze panes on).
Question: WITHOUT hiding rows, how do I position the selected cell at the top of the scrollable section?
Any help appreciated -- code is shown below.
Denis
Code:
Sub ArrayNav()
Dim c As Range
Dim i As Integer, R As Long
Dim ListPos As Integer
Dim TopRow As Long
Dim BottomRow As Long
Dim MyVar()
Application.ScreenUpdating = False
'find the position in the list
Range("A10").Calculate 'Workbook calculation is set to Manual
ListPos = Range("A10").Value
'this routine builds an array of all heading rows in Col A, plus last used cell.
R = Range("A65536").End(xlUp).Row
Range("A:A").SpecialCells(xlCellTypeConstants).Select
i = 0
For Each c In Selection
If c.Interior.ColorIndex = 6 Then 'Yellow background=heading
i = i + 1
ReDim Preserve MyVar(i)
MyVar(i) = c.Row
End If
Next c
i = i + 1
ReDim Preserve MyVar(i) 'Add value for last used row
MyVar(i) = R
Range("A11").Select
BottomRow = MyVar(ListPos)
Cells(R, 1).Select
Application.ScreenUpdating = True
Cells(BottomRow, 1).Select
End Sub