Macro Comparison Doc

Chadam

New Member
Joined
Feb 14, 2017
Messages
3
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
Users
0000001
0000002

<tbody>
</tbody>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try this.

Change paths, sheet names and rows as necessary

Code:
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
 
Last edited:
Upvote 0
Thank you so much this was exactly what I needed! I read through a few times and even learned some new tricks.

Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top