VBA to put the first visible cell's value from a filter data into C2 automatically

[HongRu]

Hello!
I want when I change my filter values of Data (A2:A29), I have the first visible cell's value showing in C2 automatically.
Until now, I have a helper cell E2 with formula =SUBTOTAL(3,A3:A29) in order to launch the following VBA code of sheet1.

Private Sub Worksheet_Calculate()
    Range("C2") = Range("A3:A29").SpecialCells(xlCellTypeVisible).Cells(1)
End Sub

For example, if I filter Data (A2:A29) with values "005-2029-888" and "005-3228-185" only, I should have a result as "005-2029-888" in C2 cell automatically by VBA code. (as the 1st picture)
If I filter with values "005-2029-873" and "005-2029-888" only, it should be "005-2029-873" in C2 cell. (as the 2nd picture)

I think maybe have a better way to do the same thing without the helper cell.

Thanks.

Order_No_filter.xlsm
	A	B	C	D	E
1			Result		Helper Cell
2	Order_No		005-2027-441		27
3	005-2027-441
4	005-2027-441
5	005-2027-441
6	005-2027-441
7	005-2027-441
8	005-2029-873
9	005-2029-873
10	005-2029-873
11	005-2029-873
12	005-2029-873
13	005-2029-873
14	005-2029-888
15	005-2029-888
16	005-2029-888
17	005-2029-888
18	005-2029-888
19	005-3228-184
20	005-3228-184
21	005-3228-184
22	005-3228-184
23	005-3228-185
24	005-3228-185
25	005-3228-185
26	005-3228-185
27	005-3228-185
28	005-3228-186
29	005-3228-186
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUBTOTAL(3,A3:A29)

 

[Skyybot]

I still needed the Helper Cell to make the calculation automatic. I hid the Helper Cell column. This code is placed in the Sheet object.

Sub Show_First()
Dim rng As Range, cell As Range
Set rng = Range("Table1") 'Change Table1 to the name of your Filtered range
For Each cell In rng
    If Not cell.Height = 0 Then
        Cells(2, 3).Value = cell.Value
        Exit Sub
    Else
        Cells(2, 3).Value = "NA"
    End If
Next cell
End Sub

Private Sub Worksheet_Calculate()
Me.Show_First
End Sub