Dynamic Scrolling based on partial cell entry

Column A is sorted alpha numerically beginning in A3
A2 is the header
A1 is blank
TheData = dynamic named range, which is currently A2:T610
Panes frozen under the header row 2

I would like to begin typing into A1 and have TheData scroll based on what is being typed into A1.

So upon typing D into A1, the visible row under A2 becomes the first entry in column A beginning with D (possibly Row 32)

Then upon typing a second character, for example DE, the visible row under A2 becomes the first entry in column A beginning with DE (possibly Row 35)

Then upon typing a third character, for example DE3, the visible row under A2 becomes the first entry in column A beginning with DE3 (possibly Row 37)

and so on...

I don’t want to have to hit enter on A1 for this to happen, I want the scrolling to happen concurrent with the typing,
Because on hitting enter, I want to clear A1 and jump to the first visible cell under A2, which should be the data I’m looking for.

Is this possible?

Hello Carstowal,

The easiest way to accomplish this is by using a Control Toolbox ComboBox. Once you have placed the combo box on row 1, add the following code to the worksheet it is on. The code here using column "C". Change this to whichever column you are using. The combo box is named ComboBox1. Change this if your's is different.

Worksheet Macros

Private Sub ComboBox1_Change()

  ActiveWindow.ScrollRow = ComboBox1.ListIndex + 2
  
End Sub

Private Sub Worksheet_Activate()

  Dim Rng As Range
  
    Set Rng = Range("C2", Cells(Rows.Count, "C").End(xlUp))
    ComboBox1.List = Rng.Value
    
End Sub

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S

Workbook Open Macro
Change the Sheet name, column and ComboBox name to match what you are using.

Private Sub Workbook_Open()

  Dim Rng As Range
  
    With Worksheets("Sheet1")
      Set Rng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
      ComboBox1.List = Rng.Value
    End With
    
End Sub

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

Hello carstowal,

If you post your workbook, I'll take a look to see what the problem might be. The workbook I have with this same macro functions fine. I am using Excel 2003.

Sincerely,
Leith Ross

here it is, thanks,

I have 2000 but
I tried it on another PC running 2003 and it still didn't work

Hello carstowal,

I made a correction to the Workboo_Open() event. The code to load the ComboBox was missing. The attached file works now.

Sincerely,
Leith Ross