Excel 2010: VBA Script to go to a row in a Table based on a cell value

Using:  Excel 2010

Given a Table like this but with hundreds of rows:

ID#     Title
001     Title1
004     Title4
005     Title5a
005     Title5b
017     Title17

I would like a user to be able to enter an ID# in a cell (this becomes the lookup value), say "4", and for an adjacent 'GoTo' button, when double-clicked, to move the cursor to the cell in the Table which contains the first occurrence of ID# 004 (in this example the ID#s are non unique but they are numeric as opposed to text; the leading zeros are added by the cell's custom format).

A different variant of this which I also require would, instead of going to the cell which contains the lookup value, filter the Table to show only those records with ID# 004.  I realize a user can simply click the Filter arrow under column heading ID#, deselect All and select the desired filter value but this is tedious when many users have to scroll through lists which are hundreds of numbers long, dozens of times a day.  Ideally we would have a 'Show' button adjacent to the cell in which the user types in the lookup value and a 'ShowAll' button below that which clears all filter criteria.

Thanks for your help.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
You could try adding some rows above the table and then a list drop down (validation).

The user could type or select a row to display without any filters etc.
This would be simple and VBA free.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
assuming that you have a cell for filter in the sheet in $A$3
you can add this to sheet code that will filter if that value changed

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$3" Then

Dim ii As Integer
Dim ws As Worksheet
Set ws = ActiveSheet
For ii = 5 To ws.UsedRange.Rows.Count
If InStr(CStr(ws.Cells(ii, 1)), Target.Value) > 0 Then
ws.Rows(ii).EntireRow.Hidden = False
ws.Rows(ii).EntireRow.Hidden = True
End If
End If
End Sub

Open in new window

qengAuthor Commented:
Sorry for the delay; just able to get back to this now.

Steve:  thanks, your solution does work and has the benefit of leaving the original (full table) intact, so I don't have to filter and unfilter.  The part I'm not clear on though is:  am I right in assuming that for this to work, the validation list in the rows added above the main table needs to be built with the ID#s.  For example, if I added a row to your example, with ID#010, the 'filter' validation list in the rows above doesn't autoupdate.  My problem here is that my lists are hundreds of items long and are dynamic (the id #s change, some get added, some get deleted)
qengAuthor Commented:

I wasn't able to get the code you provided to run (I was careful to check to make sure I was putting a value in cell $A$3 to trigger "Worksheet_Change(ByVal Target As Range)".  Putting a value in that cell, changing it, hitting enter, or double-clicking wouldn't trigger the macro.  I tried making the macro public and to run it explicitly, by name, but the marco wouldn't show up.

I did develop some alternative code which does what I need though.  I'm able to filter the table to a value I type in, in a given lookup cell.  I'm also able to unfilter the table if i delete the value in the lookup cell.

What I need to do now is to open the hyperlinked filename in the second column of my Table (in my example above, assume that the Titles are hyperlinked) based on the value of 'lookup' in the first row.

So in my example, with a user typing "5" in the lookup cell, the macro would cause the table to autofilter to just the row with ID# 005 (that part I have working) and would open the hyperlinked filename "Title5a" from the adjacent cell to the right of ID#
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.