Find and delete multiple rows based on criteria

[Lauradk50]

Hi, I've spent days trying to resolve this and would appreciate any help. Have large data range up to 20,000 rows and need to find and delete duplicate headings within the rows (heading covers 9 rows separately). My code successfully finds and deletes the first row, but not the 8 subsequent rows. Error lies in the row that contains "resize" script below. I cannot use filtering as each heading row has similar text to some content that is to be kept, and the only unique row within the heading is the one I used to search "Report Date:" criteria.

Sub LRowDeleteHeadings()
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
 
With Application
[INDENT]CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
[/INDENT]End With
 
With ActiveSheet
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(14, 1).Row
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
 
'Loop from Lastrow to Firstrow (bottom to top)
For Lrow = LastRow To Firstrow Step -1
[INDENT]'Check the values in the A column
    With .Cells(Lrow, "A")
[/INDENT]   
[INDENT][INDENT]If Not IsError(.Value) Then
 
            If Trim(.Value) = "Report Date:" Then _
                .Resize(1, 9).EntireRow.Delete
        End If
[/INDENT][/INDENT]     
[INDENT]End With
[/INDENT]     
[INDENT]Next Lrow
[/INDENT]End With
 
With Application
[INDENT].ScreenUpdating = True
    .Calculation = CalcMode
[/INDENT]End With
 
End Sub

 

[Peter_SSs]

Lauradk50

Welcome to the MrExcel board!

I haven't actually tested the code, but try changing the line:
.Resize(1, 9).EntireRow.Delete
to
.Resize(9, 1).EntireRow.Delete

 

[Lauradk50]

Dear Peter,

THANK YOU, THANK YOU, Thank you. It worked perfectly! Ever so grateful for you help.

Kind regards
Laura