Delete a range of Rows based on a cell value

[niha]

Hi Guys!
When I get "Completed" in column K, it becomes the active cell. After this, I want to delete the rows from the completed cell row till the row where I find the next non-empty cell in column D. In this case I want to delete rows from 8 to 12. This number would always vary.

 

[Joe4]

Based on your sample you posted, can you post what the desired output should look like?

 

[niha]

Based on your sample you posted, can you post what the desired output should look like?

These rows have been deleted and rows below it have shifted up. Something like this.

 

[Joe4]

OK, you did not give us a whole lot of data, so I made a few assumptions:
- Every instance where "Completed" is in column K has an entry in column D of that line.
- There is at least one blank row in column D between every entry.

Based on that, I think this code will do what you want:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column G with data
    lr = Cells(Rows.Count, "G").End(xlUp).Row
    
'   Loop through rows backwards from bottom to top
    For r = lr To 8 Step -1
'       Check to see if column K has "Completed" in it
        If Cells(r, "K") = "Completed" Then
'           Find bottom row to deletem using column D
            nr = Cells(r, "D").End(xlDown).Row
'           Make sure it doesn't go past last row
            If nr > lr Then nr = lr
'           Delete rows
            Rows(r & ":" & nr - 1).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[niha]

Yup!! Works Perfectly
Thnaks a lot Joe4

 

[Joe4]

You are welcome.