Jumping within a sheet, maybe from a list, drop down???

[asallwey]

I have a spreadsheet with multiple sheets. On one sheet, call it sheet1, I have categories in ColA in alphabetical order, each of which have any number of data lines using ColA-Q. New categories are occasionally added, in alpha order.

Currently to find a category one has to scroll down the sheet.

I would like to have something like a category drop down list at the top that a user can select from and jump to that section of the sheet. I've used drop downs, hyperlinks, and range names in other places, but am stumped for a solution in this application. (It would have simple years ago when I worked in databases... .)

Any suggestions?

Alex

 

[Crystalyzer]

Why not use the autofilter?

 

[asallwey]

Ok, but not really what I'm looking for. Still requires the user to be able to spell out enough if there are similar starting categories. Be better if there is a list type.

 

[asallwey]

I've found a solution to my question that seems to work ok. I inserted a worksheet_change code sub focused on the data validation list box.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim V8 As Range, v As String, r As Range
Set A3 = Range("A3")
If Intersect(Target, A3) Is Nothing Then Exit Sub
v = A3.Value
Set r = Range("S:S").Find(what:=v, After:=Range("S1"))
' column S has the category list
Application.Goto Range("A" & r.Offset(0, 1).Value)
End Sub

I tried a Forms list box and it didn't work. So I now can have a selection box, and when a category choice is made it jumps to it. Right now it seems to be at the bottom of the screen, but I can live with that. I will also have to update line number for categories if any changes are made. But changes are not frequent, and are several at one time, so manageable.

If anyone has suggestions on how to make the active cell at the top of the screen I would appreciate it. I will mark this topic as solved in a day if no ideas.

Alex

 

[Gokhan Aycan]

You can use:

ActiveWindow.SmallScroll Down:=ActiveCell.Row-ActiveWindow.VisibleRange.Row

 

[asallwey]

Thanks for that line, it works really nice. Just what I needed.