Macro Comparison Doc

[Chadam]

I am working on a process to remove inactive users from a system that we are trying to automate. The problem is we decided we want a 30 day buffer on deleting users and our current system does not make that easy.

I can export a list of all inactive users ever and a list of users made inactive in the past 30 days but not a list of users made inactive older than 30 days. I know using a basic vlookup and then remove the matches but we intend to run this process nightly and automate it so that is not the best method. I am fairly new to macros and only have basic knowledge but it seems like this should be easy to do with a macro.

-Excel1.xlsm opens "AllInactiveUsers.xlsx" and "Last30InactiveUser.xlsx"
-Writes a vlookup in Column B comparing Column A in AllInactiveUsers to Column A [ =VLOOKUP(A2,[Last30InactiveUser.xlsx]Sheet1!$A:$A,1,FALSE) ]
-Removes all matches from AllInactiveUsers
-Saves AllInactiveUsers
-Closes all documents

We have a software that can open "Excel1.xlsm" and I am pretty sure I can set it up to auto-run when opened.

Both sheets produce documents with a running list of users in column A like below. The list of users change between each month so I cannot define the number of rows required for the vlookup. If any of this needs clarity I can try to help where needed
[TABLE="width: 500"]
<tbody>[TR]
[TD]Users[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0000001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0000002[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[portews]

Try this.

Change paths, sheet names and rows as necessary

Sub InactiveUsers()
Dim wb As Workbook
Dim Rng As Range
Dim i, LastRow As Long
Dim AllUsers, LastMonth As String
Dim TestStr, MessText, FilePath, TestFile, x As String


[COLOR=#00ff00]'set the path for the files.  Change as necessary[/COLOR]
FilePath = "C:\"
[COLOR=#00ff00]'set the file names[/COLOR]
AllUsers = "AllInactiveUsers.xlsx"
LastMonth = "Last30InactiveUser.xlsx"
MessText = ""


[COLOR=#00ff00]'open both files[/COLOR]
For i = 1 To 2
[COLOR=#00ff00]    'set the filename to test[/COLOR]
    Select Case i
    Case 1
        TestFile = AllUsers
    Case 2
        TestFile = LastMonth
    End Select
    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath & TestFile)
    On Error GoTo 0
    If TestStr = "" Then
        MessText = MessText & TestFile & " File doesn't exist. "
    Else
        Workbooks.Open Filename:=FilePath & TestFile
    End If
Next i
If MessText > "" Then x = MsgBox(MessText, vbCritical, "File not found"): End
[COLOR=#00ff00]'find how many rows we have[/COLOR]
LastRow = Workbooks(LastMonth).Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
[COLOR=#00ff00]'loop thru last 30days.[/COLOR]
For i = 1 To LastRow   [COLOR=#00ff00]'change 1 to first row of users[/COLOR]
[COLOR=#00ff00]'find match in All users[/COLOR]
        If Trim(Workbooks(LastMonth).Sheets("Sheet1").Cells(i, 1)) <> "" Then
            With Workbooks(AllUsers).Sheets("Sheet1").Range("A:A")
                Set Rng = .Find(What:=Workbooks(LastMonth).Sheets("Sheet1").Cells(i, 1), _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
[COLOR=#00ff00]                'if it does find a match[/COLOR]
                If Not Rng Is Nothing Then
[COLOR=#00ff00]                    'delete the row in All users[/COLOR]
                    Rng.EntireRow.Delete
                End If
            End With
        End If
Next i
[COLOR=#00ff00]'save All Users and close[/COLOR]
Workbooks(AllUsers).Close True
[COLOR=#00ff00]'close Last 30 Days without save[/COLOR]
Workbooks(LastMonth).Close False
End Sub

 

[Chadam]

Thank you so much this was exactly what I needed! I read through a few times and even learned some new tricks.

Thanks again