I need a macro that can go down a spreadsheet and when it finds a certian
word to delete that row and the four rows below the first. Any suggestions?

Put the two lists one after each other on a new sheet. Then go to worksheet
menu

Data - Advancefilter

Select Unique Values Only and Copy to new range. This will give you unique
values. If you need a macro turn on the macro record while performing
operations.

"Cheffred" wrote:

I need a macro that can go down a spreadsheet and when it finds a certian
word to delete that row and the four rows below the first. Any suggestions?

The following code should do what you want. Change the lines marked
with <<<< to the appropriate values. WS is the worksheet containing
the data to test. StartRow is the row number of the first data
element. ColLetter is the column letter of the column with the data.
FindWhat is the text to find. CompareMethod indicates whether the text
comparison is case sensitive or case insensitive.

Sub AAA()
Dim LastRow As Long
Dim StartRow As Long
Dim RowNdx As Long
Dim ColLetter As String
Dim WS As Worksheet
Dim FindWhat As String
Dim CompareMethod As VbCompareMethod

Set WS = Worksheets("Sheet1") '<<< CHANGE
StartRow = 1 '<<< CHANGE
ColLetter = "A" '<<< CHANGE
FindWhat = "abc" '<<< CHANGE
CompareMethod = vbBinaryCompare '<<< CHANGE
' vbBinaryCompare for case-sensitive
' vbTextCompare to ignore upper/lower
With WS
LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row
For RowNdx = LastRow To StartRow Step -1
If StrComp(.Cells(RowNdx, ColLetter).Value, _
FindWhat, CompareMethod) = 0 Then
.Rows(RowNdx).Resize(5, 1).EntireRow.Delete
End If
Next RowNdx
End With
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 13 Mar 2009 08:55:01 -0700, Cheffred
wrote:

I need a macro that can go down a spreadsheet and when it finds a certian
word to delete that row and the four rows below the first. Any suggestions?

Thanks, that worked great!

As a variation, how would I put this Concatenate function in instead of
deleting the rows?


Range("A9").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[1],R[1]C[1],R[1]C[2],R[1]C[3],R[1]C[4])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B9:J9").Select
Application.CutCopyMode = False
Selection.ClearContents


"Chip Pearson" wrote:

The following code should do what you want. Change the lines marked
with <<<< to the appropriate values. WS is the worksheet containing
the data to test. StartRow is the row number of the first data
element. ColLetter is the column letter of the column with the data.
FindWhat is the text to find. CompareMethod indicates whether the text
comparison is case sensitive or case insensitive.

Sub AAA()
Dim LastRow As Long
Dim StartRow As Long
Dim RowNdx As Long
Dim ColLetter As String
Dim WS As Worksheet
Dim FindWhat As String
Dim CompareMethod As VbCompareMethod

Set WS = Worksheets("Sheet1") '<<< CHANGE
StartRow = 1 '<<< CHANGE
ColLetter = "A" '<<< CHANGE
FindWhat = "abc" '<<< CHANGE
CompareMethod = vbBinaryCompare '<<< CHANGE
' vbBinaryCompare for case-sensitive
' vbTextCompare to ignore upper/lower
With WS
LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row
For RowNdx = LastRow To StartRow Step -1
If StrComp(.Cells(RowNdx, ColLetter).Value, _
FindWhat, CompareMethod) = 0 Then
.Rows(RowNdx).Resize(5, 1).EntireRow.Delete
End If
Next RowNdx
End With
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 13 Mar 2009 08:55:01 -0700, Cheffred
wrote:

I need a macro that can go down a spreadsheet and when it finds a certian
word to delete that row and the four rows below the first. Any suggestions?