Last night a macro saved my life!

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:

http://www.excelforum.com/excel-programming/629530-how-do-i-delete-all-rows-containing-a-certain-variable-text-string.html

gb

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>