This is my first question here, and I'm still an amateur, but I am trying to accomplish the following using VBA:

Note: I do not have any existing code I did myself. I tried pulling a hybrid together from several codes I found that did some similar things, but I got nothing to work in the end.

I have a list of all users in Sheet1 Column A, and a list of specific users in Sheet2 Column A.

I need to accomplish the following with my code:

1. Loop through each row in Sheet1, Column A
2. Check if value is present in the list of Sheet2, Column A
3. If value is not there, then delete the row and continue to next

So in the end I want to just keep those values in that sheet1 column A that are also in the second sheet.

Could anyone assist with a simple code? I did try searching around, but wasn't able to find one that worked exactly like this.

Thanks a lot in advance!

Sub DelColumn()

Dim PUsersList As Worksheet
Dim SUsersCheck As Worksheet
Dim lrM As Long
Dim lrS As Long
Dim i As Long, m, MLookup As Range, SLookup As Range

Set PUsers= ThisWorkbook.Worksheets("Sheet1")
Set SUsers = ThisWorkbook.Worksheets("Sheet2")

Set PLookup = PUsers.Columns(1)  '<< reference list
Set SLookup = SUsers.Columns(1) '<< reference list

lrM = PUsers.Cells(PUsers.Rows.Count, "A").End(xlUp).Row
lrS = SUsers.Cells(SUsers.Rows.Count, "A").End(xlUp).Row

' here is where I'm stuck...

MsgBox "Update Complete"

End Sub

CodePudding user response：

One workbook with 2 sheets. I made up some sample usernames and remove two of the usernames from sheet 2. I added comments to the code to help in explaining.

Sheet1 image

Sheet2 image

Sub deleteUsersThatDoNotExists()
Dim FindString As String
Dim Rng As Range
 
 'get the value from the first row. in column A
 LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'loop the rows in sheet 1
 For i = 1 To LastRow

 'get the value from sheet 1 to search in sheet 2
  searchValue = Cells(i, 1)
 
 'get current address in the loop for the current cell.
  currentAddress = Cells(i, 1).Address

    FindString = searchValue
    If Trim(FindString) <> "" Then
        With Sheets("Sheet2").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
             'found the user.
               Debug.Print (searchValue   " "   "found")
            Else
              'did not find the user so lets delete but notice that i use clear and not delete
              'so that we dont end up shifting rows up.
             Range(currentAddress).Clear
             
            End If
        End With
    End If
  Next

End Sub