Do to your follow up question - Can I run this Macro faster? - I'm submitting my answer here and voting to close that question as a duplicate.
If I understand you, you want to take all the values in column H and delete them from column E? I'd do that with some arrays to speed it up -
Option Explicit Sub DoTheThing() Application.ScreenUpdating = False Dim lastrow As Integer 'Find last row in column H to size our array lastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).row 'Declare the array and then resize it to fit column H Dim varkeep() As Variant ReDim varkeep(lastrow - 1) 'Load column H into the array Dim i As Integer For i = 0 To lastrow - 1 varkeep(i) = Range("H" & i + 1) Next Dim member As Variant 'find last row in column E lastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).row 'loop each cell in column E starting in row 2 ending in lastrow For i = 2 To lastrow 'Make a new array Dim myArray As Variant 'Load the cell into the array myArray = Split(Cells(i, 5), " ") Dim k As Integer 'for each member of this array For k = LBound(myArray) To UBound(myArray) member = myArray(k) 'call the contains function to check if the member exists in column H If Contains(varkeep, member) Then 'if it does, set it to nothing myArray(k) = vbNullString End If Next 'let's reprint the array to the cell before moving on to the next cell in column E Cells(i, 5) = Trim(Join(myArray, " ")) Next Application.ScreenUpdating = True End Sub Function Contains(arr As Variant, m As Variant) As Boolean Dim tf As Boolean 'Start as false tf = False Dim j As Integer 'Search for the member in the keeparray For j = LBound(arr) To UBound(arr) If arr(j) = m Then 'if it's found, TRUE tf = True Exit For End If Next j 'Return the function as true or false for the if statement Contains = tf End Function
This creates an array out of column H. Then it goes through each cell in column E, parses it to an array, searches each member of that array against the keep array and if found, deletes that member of the array. After going through the cell, it reprints the array with the found ones missing.
Arrays are generally faster than going item by item, but additionally, we're creating our own function rather than using the slow Find and Replace method. The only issue being that there may be extra spaces within the data. If so, we can run a quick find and replace for that. I found it easier to set the members of the array to nothing rather than to re-size the array and move the elements.
Just for completeness' sake, here's a routine that removes extra spaces from column E
Sub ConsecSpace() Dim c As Range Dim lastrow As Integer lastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row Dim strValue As String For Each c In Range("E2:E" & lastrow) strValue = c.Value Do While InStr(1, strValue, " ") strValue = Replace(strValue, " ", " ") Loop c = strValue Next End Sub