Delete Entire Row if cell contains specified value

Hi, I'm very new to VBA and have come up against a situation that is beyond me and my research through the forums.

I need to keep everything in rows 1,2 and 3.
After that, I need to delete all the rows up until the first time a cell in column D contains a percentage and shift all the rows after that up with it. The amount of rows that will need to be deleted changes everyday that I update.

So when the macro is completed, Row 4 should now be a row that contains a percentage in column D.

I have attached a sample of the project. In this sample, Row 28 should end up being Row 4 after the macro runs.

Thanks
Peter

Hi kosti,

Thanks for being my 2,000th posting!!

Try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(4, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(LEFT(CELL(""format"",D4),1)=""P"","""",NA())" 'http://www.ozgrid.com/forum/showthread.php?t=80506
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With
    
End Sub

Regards,

Robert

if i am understanding the question properly

...not sure if my logic is correct for all situations you are going to face but it works with example provided

try this

Sub abc()
 With Application
    .ScreenUpdating = False
    For i = Cells(Rows.Count, "e").End(xlUp).Row To 5 Step -1
       If Not Cells(i, "e").NumberFormat = "0%" Then
           Cells(i, "e").EntireRow.Delete
       End If
    Next
    .ScreenUpdating = True
 End With
End Sub

Wow thank you to all the reply's. Worked like a charm.