VBA- Delete a specific row with text+delete the row before+ delete the row after

[tzav]

Hi,

I need to delete with VBA every row that contains the value "00/00/0000"+ one row before and one row after (3 rows in total).
the values are in column m.

For example:
row 1
row 2
row 3- 00/00/0000
row 4
row 5
row 6- 00/00/0000
row 7
row 8
etc...

In this example, the result needs to be that only rows 1+8 stay.

Thanks

 

[igold]

Perhaps this for values in column 1

Sub DeleteFormat()

    Dim i  As Long
    
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        If Cells(i, 1) = Format(Cells(i, 1), "##/##/####") Then
            Cells(i - 1, 1).Resize(3).EntireRow.Delete
        End If
    Next
    
End Sub

 

[Joe4]

What columns are we looking in? Will it always be column A?
Where exactly does this value of "00/00/0000" show up? Is it always at the end of the string?
Are we looking for ANY date, or just literally "00/00/0000"?

 

[igold]

I am sorry, I missed that you wrote that the values were in Column M. Please try this instead...

Sub DeleteFormat()

    Dim i  As Long
    
    For i = Cells(Rows.Count, 13).End(xlUp).Row To 1 Step -1
        If Cells(i, 13) = Format(Cells(i, 13), "##/##/####") Then
            Cells(i - 1, 13).Resize(3).EntireRow.Delete
        End If
    Next
    
End Sub

 

[tzav]

I am sorry, I missed that you wrote that the values were in Column M. Please try this instead...

Sub DeleteFormat()

    Dim i  As Long
   
    For i = Cells(Rows.Count, 13).End(xlUp).Row To 1 Step -1
        If Cells(i, 13) = Format(Cells(i, 13), "##/##/####") Then
            Cells(i - 1, 13).Resize(3).EntireRow.Delete
        End If
    Next
   
End Sub

Thanks but when I tried, it deleted all the cells in the column.

 

[igold]

Perhaps you need to show us a better example of your data. This code worked on the sample you provided in your Post #1.

 

[tzav]

What columns are we looking in? Will it always be column A?
Where exactly does this value of "00/00/0000" show up? Is it always at the end of the string?
Are we looking for ANY date, or just literally "00/00/0000"?

Column m.
The string only includes these figures, nothing before or after in the same cell.
Not any date. Other dates should stay. Only literally 00/00/0000. Delete this row+row before+ row after (3 rows), every time it's on the list.

 

[Joe4]

Updating igold's code like this should do what you want:

Sub DeleteZeroDates()

    Dim i  As Long
    
    For i = Cells(Rows.Count, 13).End(xlUp).Row To 1 Step -1
        If Right(Trim(Cells(i, 13)), 10) = "00/00/0000" Then
            Cells(i - 1, 13).Resize(3).EntireRow.Delete
        End If
    Next
    
End Sub

 

[tzav]

Perhaps you need to show us a better example of your data. This code worked on the sample you provided in your Post #1.

A screen print is attached. The red cells need to be deleted, and the green need to stay.

 

[igold]

According to Post #9, the code that @Joe4 posted in Post #8, should work...