Macro help, Find text in column A then delete 8 rows above

[richiwatts]

Hi,

I wonder if someone can help me with a Macro

In column A find cell containing the term "Message text" delete that row and then 8 rows above it if there are 8 rows above (The first instance if "Message text" in my files only has 2 rows above but the rest all have 8)
Then delete column B
Then save as UTF-8 tabbed text.

It is taking me too long to do it manually.

 

[hallingh]

This will accomplish the first portion:

Sub DeleteRows()
Dim a As Long
With ActiveSheet
For a = 1 To .Range("A" & Rows.Count).End(xlUp).Row
If .Range("a" & a).Value = "Message Text" Then
If a < 9 Then
Else
.Range("a" & a & ":a" & (a - 8)).EntireRow.Delete
End If
End If
Next a
End With

End Sub

You don't say what you want to do if there is less than 8 rows above Message Text, so I just left that part blank and figured you could do whatever you wanted with it.

I don't know how to save it as UTF-8 tabbed text, though. Figured I'd give you what I could. Sry if this isn't helpful.

HTH
Hank

 

[texasalynn]

try this

Sub MsgTxt()
Dim x As Long, i As Long
Dim rFound As Variant
x = WorksheetFunction.CountIf(Range("B:B"), "Message Text")
If x = 0 Then Exit Sub
On Error Resume Next
For i = 1 To x
    Set rFound = Cells.Find("Message Text", After:=ActiveCell, SearchDirection:=xlNext)
    If rFound Is Nothing Then GoTo 0
        rFound.Activate
        z = rFound.Row
        If z > 8 Then
                Range(("A" & z - 8 & " :A" & z)).EntireRow.Delete
        End If
        If z = 2 Then
                Rows(z).EntireRow.Delete
        End If
Next i
0
Columns("A").Delete
MsgBox "Done"
End Sub

 

[richiwatts]

Neither of these really did the trick.
What about a differnet way. The only rows I want to keep in the file are the rows exactly after the rows that contains the word "Message text" in column A. All other rows need to be deleted.

 

[hallingh]

Try:

Sub DeleteRows()
Dim a As Long
a = 1
With ActiveSheet
Do while a < .Range("A" & Rows.Count).End(xlUp).Row
If .Range("a" & a).Value <> "Message Text" Then
.Range("a" & a).EntireRow.Delete
a = a + 1
Else
.Range("a" & a).EntireRow.Delete
a = a + 2
End If
Loop
End With

End Sub

This is untested so save a copy of your workbook first.

HTH

Hank