Excel - How to automatically remove names in a list

[althea]

I need to create a macro to automatically remove names in a list IF the names exist in another list. Both lists are on the same sheet. Has anyone ever done this? Any ideas?

 

[Zathras]

Here is one way (modify the row and column constants to fit your situation):

Option Explicit

Sub RemoveFromListA()
Const COL_LIST_UPDATE = 1  ' Column "A"
Const COL_LIST_MASTER = 3  ' Column "C"
Const ROW_FIRST_UPDATE = 2 ' Data starts in row 2
Const ROW_FIRST_MASTER = 2 ' Data starts in row 2
Dim MasterList As Range
Dim FoundName As Range
Dim nLastMasterRow As Long
Dim nLastUpdateRow As Long
Dim nRow As Long
Dim sName As String

  nLastMasterRow = Cells(65536, COL_LIST_MASTER).End(xlUp).Row
  nLastUpdateRow = Cells(65536, COL_LIST_UPDATE).End(xlUp).Row
  Set MasterList = Range(Cells(ROW_FIRST_MASTER, COL_LIST_MASTER), _
                    Cells(nLastMasterRow, COL_LIST_MASTER))
  
  For nRow = nLastUpdateRow To ROW_FIRST_UPDATE Step -1
    sName = Cells(nRow, COL_LIST_UPDATE).Value
    Set FoundName = MasterList.Find(sName)
    If Not FoundName Is Nothing Then
      Cells(nRow, COL_LIST_UPDATE).Delete xlShiftUp
    End If
  Next nRow
  Set FoundName = Nothing
End Sub

 

[althea]

Thanks for the code. Col List Update - would that be a cell range of my master list? If you could explain what I should replace that would be helpful. Thanks!

 

[Zathras]

The example assumes that each list is contained in a single column. The "master" list is coded for column 3 (column "C&quot and the list to be deleted from is coded for column 1 (column "A&quot

If your lists span more than one column each, then the code will require major adjustment.

COL_LIST_UPDATE is defined as a constant to allow the code to be more easily understood. It is the same as using the number 1 (in this example) and identifies the list to be updated as found in column 1 (columm "A&quot.

  Cells(nRow, COL_LIST_UPDATE).Delete xlShiftUp

is easier to understand than

  Cells(nRow, 1).Delete xlShiftUp

And, more importantly, if the list is moved to another column, all you have to do is change the value of the constant. You don't have to go thru all of the code and look at each time the number 1 appears and ask yourself whether it should be changed to something else.

 

[althea]

My list has 2 columns. One column is a number and the other is a name. I am randomly generating 10 numbers which generates 10 names with the VLOOKUP function. I just need the names picked to be deleted from the master list when used. What would I have to modify?
Thanks so much!

 

[Zathras]

I can be of more help if you tell me exactly which columns have what. I could guess numbers in "A" names in "B" but I was already turned around by the use of terms "a list" and "another list" and thinking that the "Master List" was the reference and that names were to be removed from the list that is not the "Master List." I think I understand now that you are removing names from the "Master List"

Best we start over. Maybe you could give me an example or two.

BTW, are you using range names? If so, be aware that deleting cells can cause the range to be re-defined. So that if you have a 10-row range and delete a row, you could end up with a 9-row range. That would lead to unexpected results later.

 

[althea]

I'm not using named ranges. Here is an example of my Master list (I will actually have about 200 names):
A B
1 Deard, Brett
2 Weiss, Barb
3 Smith, Cindy
4 Davis, Mark
5 Bourte, Martha
6 Nolan, Deb
7 Rivas, Steve

To generate the next list, I am using a function called RANDBETWEEN to randomly generate ten numbers from a list of 200 in the master list. I am using VLOOKUP function to return the name that matches up with the number that is randomly generated. Once these names are picked, I need to remove them from the master list so that they won't be picked next time the RANDBETWEEN function is calculated. The result of this second list looks like this (but I have 10 numbers/names):

D E
5 Bourte, Martha
2 Weiss, Barb
3 Smith, Cindy
6 Nolan, Deb
1 Deard, Brett

Thanks so much for any help!

 

[Zathras]

Ok, I think I understand now. Here is a routine that you can include in your project to remove the selected name from the list:

Sub RemoveFromList(AName As String)
Const COL_NAMES = 2  ' Column "B"
Const COL_DELETE_FROM = 1  ' Column "A"
Const COL_DELETE_THRU = 2  ' Column "B"
Const ROW_FIRST_DATA = 2 ' Data starts in row 2
Dim List As Range
Dim c As Range
Dim nLastRow As Long
Dim sName As String
  nLastRow = Cells(65536, COL_NAMES).End(xlUp).Row
  Set List = Range(Cells(ROW_FIRST_DATA, COL_NAMES), _
                    Cells(nLastRow, COL_NAMES))
  Set c = List.Find(AName)
  If Not c Is Nothing Then
    Range(Cells(c.Row, COL_DELETE_FROM), _
          Cells(c.Row, COL_DELETE_THRU)).Delete xlShiftUp
  End If
  Set c = Nothing
  Set List = Nothing
End Sub

Here is a routine to demo/test the above code:

Sub Demo()
Dim sName As String
  sName = "Bourte, Martha"
  RemoveFromList sName
End Sub

Here is a procedure to delete all names from the list that are currently in another list, using the first sub to do the work:

Sub RemoveAllNames()
Const COL_NAMES = 5 ' Column "E"
Const ROW_FIRST_DATA = 2
Dim List As Range
Dim c As Range
Dim nLastRow As Long
  nLastRow = Cells(65536, COL_NAMES).End(xlUp).Row
  Set List = Range(Cells(ROW_FIRST_DATA, COL_NAMES), _
                    Cells(nLastRow, COL_NAMES))
  For Each c In List
    RemoveFromList c.Text
  Next c
  Set List = Nothing
End Sub

Programming note: When a constant is defined between the Sub and End Sub (or Function and End Function) it is only "visible" inside that routine. Same with variables (Dim statements). That is why the same variable and constant names can be used in two different routines and not have a conflict.

 

[althea]

Thanks so much! It works great! Since I used the 2 functions, now I have to modify the functions to show the new cell range of the list. Thanks!