You will need to use a user defined function in VBA to determine the last used column location with an X value. Once you have the column, you can apply this to the row number with the dates to get the last date either in VBA or via a formula INDIRECT.
The user defined function will take the row range such as:
VBA Code:
Function LASTCOL(rngRow As Range) As Variant Dim tmpRange As Range Dim i As Integer, cnt As Integer Application.Volatile Set tmpRange = rngRow.Rows(1).EntireRow Set tmpRange = Intersect(tmpRange.Parent.UsedRange, tmpRange) cnt = tmpRange.Count For i = cnt To 1 Step -1 If Not IsEmpty(tmpRange(i)) Then LASTCOL = tmpRange(i).Value Exit Function End If Next i End Function
