Last Row in Excel VBA Evaluate

Question

I am using the below code:

Sub Evaluation_Formula()
    Dim i As Long
    With Worksheets("Sheet1")
        i = .Evaluate("MIN(IF((LEFT($B$1:$B$89,5)*1)=C1,$B$1:$B$89,""""))")
        .Range("F3").Value2 = i
    End With
End Sub

However, the formula is limited to B89, how can I use Last Row in Column B in the formula ?

Answer 1

I guess this will post proper code:

Sub Evaluation_Formula_w_LR()
Dim i As Long
Dim LR As Long
With Worksheets("Sheet1")
    LR = .Cells(.Rows.Count, 2).End(xlUp).Row
    i = Evaluate("MIN(IF((LEFT($B$1:$B$" & LR & ",5)*1)=C1,$B$1:$B$" & LR & ",""""))")
    .Range("F10").Value2 = i
End With
End Sub