Macro to delete row based on criteria (first few characters of string)

[delapp]

Hi, Newbie here and looking for a macro code to delete the header off a
report. This report is multiple pages long and have imported into
Excel. Obviously every 60 rows or so, the report header shows up
again. I wish to delete the header and was hoping to build a macro to
do that. Wanted it to match the characters "RPS677" which is the first
6 characters of the string and then once it matches that, then delete
the next 7 rows (including the one with the RPS677 in it). Below is
an example of the report. I am putting R1-R12 to show the seperate
rows. Those numbers are not part of the original spreadsheet

R1 2051619 127 119 09:51 119 14:51 00025 00142
R2 DREAS DCAJT
R3 RPS677 BATCH STATUS OPERATOR REPORT
R4 SORTED BY BOX NUMBER AND BATCH NUMBER
R5 DEPOSIT JULIAN DATE: 121
R6 ------------------------------------------
R7 BOX BAT PREP DATA ENTERED
R8 NUMBER NBR JUL TIME JUL TIME COUNT KEY
R9 OPERATOR OPERATOR
R10 --------------------------------------------
R11 2051619 128 119 09:51 119 15:16 00025 00138
R12 DREAS DCAJT

I want to delete R3-R10 and was hoping to use the RPS677 as the
criteria to find that row, then delete the next 7. This would bring
the row with "2051619 128" up to R3. I can only match the first few
characters of the RPS677 string because at the end of that string it a
page number, which changes with each page.

Any help would be appreciated.

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim nCalculation

With Application
.ScreenUpdating = False
nCalculation = .Calculation
.Calculation = xlCalculationManual
End With

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Left(Cells(i, "A"), 6).Value = "RPS677" Then
Rows(i).Resize(7).Delete
End If
Next i

With Application
.Calculation = nCalculation
.ScreenUpdating = True
End With

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[delapp]

Thanks for the quick response. I am getting a run-time error '424' :
Object required. When I go to debug, it's stopping at the "If
Left(Cells(i, "A"), 6).Value = "RPS677" Then" part of the code.

Any words of advice?

Thanks in advance

 

[delapp]

Thanks for the quick response. I am getting a run-time error '424' :
Object required. When I go to debug, it's stopping at the "If
Left(Cells(i, "A"), 6).Value = "RPS677" Then" part of the code.

Any words of advice?

Thanks in advance

 

[Bob Phillips]

Syntax wrong

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim nCalculation

With Application
.ScreenUpdating = False
nCalculation = .Calculation
.Calculation = xlCalculationManual
End With

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Left(Cells(i, "A").Value, 6) = "RPS677" Then
Rows(i).Resize(7).Delete
End If
Next i

With Application
.Calculation = nCalculation
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[delapp]

Works like a charm. I appreciate your help!