Delete Row and copy/paste other rows if Cell Equals

[helpexcel]

Hi,

I'm not sure if this is doable. I have a Validation list in column D. If Column D = "Cancel" than I would like for that row to be deleted, and the other rows copied and pasted to fill the blank row. My list only goes from rows 5:30 and i have data under the list, so i don't want the rows deleted and other rows moved up.
I also have a Validation list feeding column A. So if a row is deleted, I'd like to have to value from column A moved from the list in column AA to the list in column AB.

Hope that makes sense. Thanks!!