I'm trying to delete rows which don't contain new words.
What I do:
- Select multiple rows manually
- Run macro, which checks each row and adds new words from it to a dictionary. If there are no new words - the row should be deleted.
The problem: When macro deletes a row, it should go to the next row with "Next cell", but it skips one.
I need your help because I have no Idea how to make it work in VBA (newbie here). How to prevent that skipping and process each row in selection?
Demo data:
A B
A B C
C B
A C
A B F
My Result:
A B
A B C
A C
A B F
Should be:
A B
A B C
A B F
Code:
Sub clean_keys()
' unique words
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
For Each cell In Selection
Dim strArray() As String
Dim intCount As Integer
strArray = Split(cell.Value, " ")
Dim intWords As Integer
intWords = 0
For intCount = LBound(strArray) To UBound(strArray)
If dict.Exists(Trim(strArray(intCount))) Then
dict(Trim(strArray(intCount))) = dict(Trim(strArray(intCount))) + 1
Else
dict.Add Key:=Trim(strArray(intCount)), Item:=1
intWords = intWords + 1
End If
Next
If intWords = 0 Then
cell.EntireRow.Delete
End If
Next cell
End Sub
Always run from the bottom to the top when deleting rows or you risk skipping rows (as you have noticed).