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

[[email protected]]

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)

<[email protected]> wrote in message
news:[email protected]...
> 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.
>

[[email protected]]

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

[[email protected]]

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)

<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

Works like a charm. I appreciate your help!