Use Excel to select the most recent row from a CSV?

Question

I have a CSV that starts with a date/time field and I need to import, and select the most recent row and refer to it in my formulas.

I'm experimenting with Pivot Tables, and just now Rank and VLookup.

What is the best way to go about accessing the most recent row, and saving it in a fixed location?

Answer 1

The solution below involves a macro which, according to your tags, is not the solution you seek. However, it uses worksheet functions and you may be able to create a worksheet solution depending on the nature of the CSV import.

I hope one or other of these solutions works for you.

Note: neither solution depends on the data being sorted.

Explanation of VBA solution and possible worksheet equivalent

The step in the solution are:

Find the bottom row of the data. The keyboard equivalent of the VBA is: Go to bottom of column A then go up to first non-empty cell. However, you may not need a worksheet equivalent of this step.

I cannot think of a worksheet equivalent of the next statements which convert date strings to date numbers. This conversion will not be necessary if your import performs this conversion for you.

The next step is to find the maximum value in column A. Excel dates are numbers so MAX will do this for you. If you know that there will never be more than 2000 rows you could use:

=MAX(A2:A2000)

The final step is to identify the row for the maximum value. If the maximum value is in cell X1 then:

=1 + MATCH(X1,A2:A2000,0)

is the keyboard equivalent of this step.

VBA Solution

Option Explicit
Sub FindLatestDate()

  Dim DateCol() As Variant
  Dim DateMax As Date
  Dim RngData As String
  Dim RowDateMax As Integer
  Dim RowMax As Integer

  With Sheets("Sheet2")

    RowMax = .Cells(Rows.Count, "A").End(xlUp).Row  ' Find bottom column of data.
    RngData = "A2:A" & RowMax                       'Assume first data row is 2.

    ' If column A contains strings that Excel can correctly
    ' interpret as dates, force Excel to do so.
    DateCol = .Range(RngData).Value
    .Range(RngData).Value = DateCol

    ' Excel dates are integer values.  Excel date/times are real values.
    ' Get the maximum value in the range.
    DateMax = Application.WorksheetFunction.Max(.Range(RngData))

    ' The use of CLng assumes the date does not include a time.
    ' Use CDbl if the date does include a time
    RowDateMax = 1 + _
       Application.WorksheetFunction.Match(CLng(DateMax), .Range(RngData), 0)

    ' RowDateMax gives you the value you seek.
    ' This code does not rely on the CSV being in date order

  End With

End Sub