Filter Rows by Username

[mrbeanyuk]

Good afternoon. I have seen a few posts that half answer this question, but none quite go the whole way...

I have found the code below which helpfully returns the username of the person executing it. What I would like to do now is filter the rows where the username matches the contents of Column B in Sheet1 of my spreadsheet.

Any thoughts would be most welcome.

Thanks

Option Explicit
  
Sub Users_Fullname()

Dim objInfo
Dim strLDAP
Dim strFullName
  
Set objInfo = CreateObject("ADSystemInfo")
strLDAP = objInfo.UserName
Set objInfo = Nothing
strFullName = GetUserName(strLDAP)
  
MsgBox "Full name of User is " & strFullName  'step to test
  
End Sub

Function GetUserName(strLDAP)
  Dim objUser
  Dim strName
  Dim arrLDAP
  Dim intIdx
  
  On Error Resume Next
  strName = ""
  Set objUser = GetObject("LDAP://" & strLDAP)
  If Err.Number = 0 Then
    strName = objUser.Get("givenName") & Chr(32) & objUser.Get("sn")
  End If
  If Err.Number <> 0 Then
    arrLDAP = Split(strLDAP, ",")
    For intIdx = 0 To UBound(arrLDAP)
      If UCase(Left(arrLDAP(intIdx), 3)) = "CN=" Then
        strName = Trim(Mid(arrLDAP(intIdx), 4))
      End If
    Next
  End If
  Set objUser = Nothing
  
  GetUserName = strName
  
End Function

 

[JackDanIce]

Try:

Sub Users_Fullname()

Dim objInfo As Object
Dim strLDAP As Object
Dim strFullName As String
Dim LR  As Long
Dim x   As Long
Dim arr()   As Variant
Set objInfo = CreateObject("ADSystemInfo")
strLDAP = objInfo.UserName
Set objInfo = Nothing
strFullName = GetUserName(strLDAP)
  
'MsgBox "Full name of User is " & strFullName  'step to test
With Sheets("Sheet1")
    LR = .Cells(.Rows.count, 2).End(xlUp).row
    .Cells(1, 2).AutoFilter Field:=1, Criteria1:=strFullName
End With

End Sub

Function GetUserName(ByRef strLDAP As Variant) As String
  Dim objUser As Object
  Dim arrLDAP As Variant
  Dim intIdx As Long
  
  On Error Resume Next
  
  Set objUser = GetObject("LDAP://" & strLDAP)
  If err.Number = 0 Then
    GetUserName = objUser.Get("givenName") & Chr(32) & objUser.Get("sn")
  Else
    arrLDAP = Split(strLDAP, ",")
    For intIdx = 0 To UBound(arrLDAP)
      If UCase(Left(arrLDAP(intIdx), 3)) = "CN=" Then GetUserName = Trim(Mid(arrLDAP(intIdx), 4))
    Next
  End If
  
  Set objUser = Nothing
  
End Function