Okay, so this Excel macro didn’t exactly save my life from a broken heart, but it certainly saved my laptop from being thrown off the balcony!
If you have a large spreadsheet with redundant data, this quick little macro searches for a string and removes every row that contains that string:
Sub EliminateRows()
‘
‘ Highlight the cells or column to be scanned for the search string and invoke ‘ this macro
‘
Dim Index As Long
Dim MatchPattern As String
Dim StartRow As Long
Dim EndRow As Long
MatchPattern = InputBox(“Enter match Pattern”)
If (MatchPattern <> “”) Then
StartRow = Selection.Row
EndRow = Selection.Row + Selection.Rows.Count – 1
For Index = EndRow To StartRow Step -1
If (Cells(Index, 1) Like “*” & MatchPattern & “*”) Then ‘ anywhere in cell
Cells(Index, 1).EntireRow.Select
Selection.Delete
End If
Next Index
End If
End Sub
I had to change Cells(Index, 1) to Cells(Index, 2) for my purposes. Your mileage may vary as well.
big ups to this site for helping my computer prolong its life:
gb