PDA

View Full Version : [SOLVED] VBA to delete rows where first 4 words of text in column A are duplicated



1819
03-26-2016, 10:55 AM
I would like a macro to delete rows where the first 4 words of text in column A are duplicated.


So:

Cell A1: I have a dream today.
Cell A2: I have a meeting today.
Cell A3: I have a dream today.
Cell A4: I have a dream today. And I will wake up tomorrow.
Cell A5: I have a meeting tomorrow.

would become:

Cell A1: I have a dream today.
Cell A2: I have a meeting today.

This was previous posted in Sep 2015, but not answered, at: http://www.excelforum.com/excel-programming-vba-macros/1105085-vba-to-delete-rows-where-first-4-words-of-text-in-column-a-are-duplicated.html

Many thanks.

Paul_Hossler
03-26-2016, 12:19 PM
I'd use a temporary helper column




Option Explicit

Sub DeleteFirstFive()
Dim r As Range
Dim v As Variant
Dim i As Long
Dim s As String

For Each r In ActiveSheet.Cells(1, 1).CurrentRegion.Cells
s = vbNullString
v = Split(r.Value, " ")

For i = LBound(v) To UBound(v)
s = s & v(i)
If i = 3 Then Exit For
Next I

r.Offset(0, 1).Value = s
Next
ActiveSheet.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=2, Header:=xlNo
ActiveSheet.Columns(2).Delete
End Sub

1819
03-26-2016, 02:45 PM
Thank you, Paul - that's terrific.

I will mark it solved on the cross-post and redirect here.