I need to find the first word of a sentence and then delete the whole column.
At the moment my code finds the word anywhere in the cell.
Code
Sub Delete_Col_by_WordSearch()
Dim Found As Range, strWord As String, Counter As Long
InRow = Application.InputBox("Enter Row Number to search through", "Comments Row - usually row 7")
strWord = Application.InputBox("Enter the word to search for.", "Delete the columns with this word", Type:=2)
If strWord = "False" Or strWord = "" Then Exit Sub 'User canceled
Set Found = Cells(InRow, 1).EntireRow.Find(strWord, , , xlPart, , xlNext, False)
If Not Found Is Nothing Then
Application.ScreenUpdating = False
Do
Found.EntireColumn.Delete
Counter = Counter + 1
Set Found = Cells(InRow, 1).EntireRow.Find(strWord, , , xlPart, , xlNext, False)
Loop Until Found Is Nothing
Application.ScreenUpdating = True
MsgBox Counter & " columns deleted.", vbInformation, "Process Complete"
Else
MsgBox "No match found for: " & strWord, vbInformation, "No Match"
End If
End Sub
Display More