Filter merged cells workaround

Question

My problem:

I have a spreadsheet with ids and names to search.
I thought I could use the filter option but the ids or names are in merged cells, so the filter option only returns the first row of data about an id or name.

My spread sheet:

    |Header 1| header 2| header 3| header 4|
    |--------------------------------------|
    |        |         |  data1  |  data1  |
    |id1     | name1   |  data2  |  data2  |
    |        |         |  data3  |  data3  |
    |--------------------------------------|
    |        |         |  data1  |  data1  |
    |id2     | name2   |  data2  |  data2  |
    |        |         |  data3  |  data3  |
    |--------------------------------------|

What I've looked into:

How to sort/filter merged cells in Excel?

Here I learned I would get all the data by unmerging the cells and giving them the same values. But there are a few problems:

1. This needs to be foolproof. It would be easy for a user to give them diferent names or ids by mistake

2. The names are often long, so merging the cells allows me to display them completely without column B being too wide

3. I have already written code to write and read this data, I would need to review how I navigate the spreadsheets

How do I avoid merging cells vertically in excel?

This post suggested replacing the merged cell by a textbox on top of the cells, this could work if I had a formula to give each cell under the textbox the value in the textbox automatically. But again, not super foolproof or clean.

The solution?

I don't actually want to filter data. I want the user to get to the right part of the sheet by using a searchbox.

I was thinking a macro could work this way:

1. Listen for the user to apply a filter on a specific sheet, specific header

2. Store the address of the first visible cell under the header in variable MyCell

3. Remove the filter

4. Select MyCell

Is this possible?

Alternative solution?

1. Listen for the user to apply a filter on a specific sheet, specific header

2. Make the 2 rows under each filtered row visible

Could also work but not necessary.

Answer 1

Thanks to @kyle for pointing me in the right dirrection. This is what I ended up doing:

|--------------------------------------|
|Header 1| header 2|                   |
|--------|---------| header 3| header 4|
|search: |         |                   |  
|--------------------------------------|
|        |         |  data1  |  data1  |
|id1     | name1   |  data2  |  data2  |
|        |         |  data3  |  data3  |
|--------------------------------------|
|        |         |  data1  |  data1  |
|id2     | name2   |  data2  |  data2  |
|        |         |  data3  |  data3  |
|--------------------------------------|

I placed a searchbox under the second header. I then inserted this code in the module page of the sheet the searchbox is in:

    Private SearchTerm As String
    Private SearchLoc As Range
    Private SearchOld As Range
    Private SearchStart As Range

    Private Sub Worksheet_Change(ByVal SearchBox As Range)

    If SearchBox.Address = Me.Range("B2").Address And Not IsEmpty(Me.Range("B2").Value) Then

    If Not SearchOld Is Nothing Then
    Set SearchStart = SearchOld
    Set SearchOld = Nothing
    Else
    Set SearchStart = Range("B2")
    End If

    If Not SearchTerm = Trim(Me.Range("B2").Value) Then
    SearchTerm = Me.Range("B2").Value
    Set SearchStart = Range("B2")
    End If

    If Trim(SearchTerm) <> "" Then

    With ActiveSheet.Range("A:A, B:B")
    Set SearchLoc = .Find(What:=SearchTerm, _
    After:=SearchStart, _
    LookIn:=xlValues, _
    Lookat:=xlPart, _
    Searchorder:=xlByRows, _
    Searchdirection:=xlNext, _
    MatchCase:=False)

    If Not SearchLoc Is Nothing Then
    Set SearchOld = SearchLoc
    Application.Goto Cells(SearchLoc.Row, 1), True
    End If

    End With
    End If
    End If

    End Sub

This code was mostly ripped borrowed from here: http://www.rondebruin.nl/win/s9/win006.htm

I also used this reference: https://msdn.microsoft.com/en-us/library/office/ff839775.aspx

What it does

This code listens for any change on the worksheet. It then detects if that change happened in B2 and B2 is not empty. Then it checks if an anterior search was equal or not to the current one. If that is the case it looks for the next instance of the same searchTerm, else it searches from the top. It then goes to the result. If the user searches for something that does not exist if goes to the top.

A word of warning: in my case the searchbox is included in the columns that are searched. So if the user searches for something that does not exist it simply goes to the searchbox. If you use this code and move the searchbox, you would need to check that it does not loop indefinitly.

Thanks again to @Kyle