Selecting the last date in a column

[bvespone]

Hello everyone,

I am working with history logs that have been imported from text files into excel. There will be several hundred of them, each fitting in one column, but of variable length. I am attempting to extract the last recorded date from the log, which is always of the number format */*/???? and then paste in onto another worksheet.

Unfortunately, the last date is not necessarily the latest date on the record, so a MAX function will not work, nor have I been able to get a MATCH function to work, as there are other dates within a single column.

I wrote a clumsy macro to handle it for the time being, but it essentially just an automation of using the "find" feature, and takes a long time when searching through hundreds of columns.

'Manually find and fill in End dates
Sheets("Dynamic Summary").Select
Range("B3").Select
Sheets("Log Data").Select
Range("A1").Select

If Selection.Offset(0, 1).Value = Empty Then

        Selection.End(xlDown).Select
     Cells.Find(What:="*/*/????", after:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False).Select
                
            Selection.Copy
            Sheets("Dynamic Summary").Select
            ActiveSheet.Paste
Else
     Selection.End(xlToRight).Value = "END"
    Do
     Selection.End(xlDown).Select
     Cells.Find(What:="*/*/????", after:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False).Select
                
            Selection.Copy
            Sheets("Dynamic Summary").Select
            ActiveSheet.Paste
            ActiveCell.Offset(0, 1).Select
        
         Sheets("Log Data").Select
         Selection.End(xlUp).Select
         ActiveCell.Offset(0, 1).Select
     Loop Until Selection = "END"
 Selection.ClearContents
 
 End If

If anyone has any thoughts about a more elegant way to handle this, I would be really appreciative.

 

[Jerry Sullivan]

Hello and welcome,

If I'm understanding your description correctly, you can find the
last cell in Col A that has a date value matching your pattern using something like this...

Sub Find_Last_Date()
    Dim rngMatch As Range
    With Sheets("Log Data").Columns(1)
        Set rngMatch = .Find(What:="*/*/????", after:=.Cells(1), _
            LookIn:=xlFormulas, LookAt:=xlWhole, _
            SearchDirection:=xlPrevious)
    End With
    If rngMatch Is Nothing Then
        MsgBox "Date pattern not found"
    Else
        MsgBox rngMatch.Value & " found in Cell: " & rngMatch.Address
    End If
End Sub

You mention that there will be several hundred of these.
Will they all be in the same workbook or just one of these per workbook?

Depending on that answer, you would probably want to have your macro step
through each worksheet or workbook and copy the last date to a single worksheet.

Otherwise your macro is just copying one match at a time with you doing manual steps
to get to each sheet which could be a very long process.

 

[bvespone]

Awesome, that worked perfectly. Each log is in a separate column of the same worksheet, so I just looped that code to look at all the columns and paste them one column to the right in the other worksheet. Here's the final code if anyone is interested.

'Find and fill in End dates
    Dim rngMatch As Range
    Dim iCol As Integer
    Dim ColMax As Integer
    
    Sheets("Log Data").Activate
    ColMax = WorksheetFunction.CountA(Range("1:1"))
    iCol = 1
    
Do
    With Sheets("Log Data").Columns(iCol)
        Set rngMatch = .Find(What:="*/*/????", after:=.Cells(1), _
            LookIn:=xlFormulas, LookAt:=xlWhole, _
            SearchDirection:=xlPrevious)
    End With
    Sheets("Dynamic Summary").Cells(3, iCol + 1).Value = rngMatch.Value
    iCol = iCol + 1
    
Loop Until iCol = ColMax

ps, I love this website