Sub UserLatestLoginDate()
'
' UserLatestLoginDate Macro
' Finds the latest login date for each user.

' The macro finds and deletes all the blank rows (if any) and failed login attempts.
' Then the worksheet is scanned from the end. Pick up the user name, compare it
' with the list of names already existing in the UserRng range. If it exists, skip to the
' next row. If not, write the user name and the login date in the UserRng row and increment
' the user row for the next entry.

    Dim LoginRng As Range, lastRow As Long, lastCol As Integer
    Dim UserRng As Range, celTxt As String, cel As Range
    Dim nameUser As String, nameLogin As String, celVal As Boolean
    
    Dim delBlRows As Long, delFRows As Long
    Dim currCel As Range
    Dim logDate As Date
    Dim i As Long, j As Integer, k As Integer
    
    'Find the last row of the worksheet range.
    lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    lastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    MsgBox (lastRow)
    Set LoginRng = ActiveSheet.Range(Cells(1, 1), Cells(lastRow, lastCol))
    'Delete all blank rows.
    For i = lastRow To 1 Step -1
        celVal = True
        For j = 1 To lastCol
            Set cel = LoginRng.Cells(i, j)
            If (cel.Value <> "") Then celVal = False
            If (cel.Value = "Failed" Or cel.Value = "failed") Then
                cel.EntireRow.Delete
                delFRows = delFRows + 1
            End If
        Next j
        If (celVal = True) Then
            LoginRng.Cells(i, j).EntireRow.Delete
            delBlRows = delBlRows + 1
        End If
    Next i
    MsgBox (delBlRows & " blank rows deleted and " & delFRows & " failed login rows deleted.")
    'Save the worksheet
    ActiveWorkbook.Save
    'Delete rows where the login attempt has failed.
    'Find the last row again after deletion of blank rows.
    lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
'    MsgBox (lastRow)
    Set LoginRng = ActiveSheet.Range(Cells(1, 1), Cells(lastRow, lastCol))

    'Set a Range in the same worksheetwhere the user data will be written.
    'Select the column beyond the last column used.
    'This range will have two columns - username and the last login date.
    k = 1
    j = 1
    
    'The user name is in the columns 10 and 11. This is trimmed and concatenated.
    For i = lastRow To 1 Step -1
        celVal = False
        celTxt = Trim(Trim(LoginRng.Cells(i, 10).Value) & " " _
                                & Trim(LoginRng.Cells(i, 11).Value))
        
        For j = 1 To lastRow
            Set UserRng = ActiveSheet.Cells(j, 13)
            If (celTxt = Trim(UserRng.Value) Or celTxt = "") Then
                celVal = True
                GoTo SkipIt
            End If
        Next
        If (celVal = False) Then
            Set currCel = ActiveSheet.Cells(k, 13)
            currCel.Value = celTxt
            'The date is in the columns 2, 3 and 5.
            currCel.Offset(0, 1).Value = CDate(Trim(LoginRng.Cells(i, 2)) & " " & 		Trim(LoginRng.Cells(i, 3)) & "," & Trim(LoginRng.Cells(i, 5)))
                                        
            k = k + 1
        End If
SkipIt:
    Next
'
End Subnd If
Sk