Copy and Paste then Delete Row

[Jamm_027]

I can usually piece together code that I have used in the past to get what I need, however today I am not having any luck.

I want to do 3 steps.

1. I want to copy any row that has a blank in column E on sheets("calendar")

2. Paste that row in the first open row based on column A on the sheet("archives")

3. then delete the row that was just copied from sheets("calendar")


jamm_027

 

[Jonmo1]

Try

Sub test()
With Sheets("Calendar").Range("E:E").SpecialCells(xlCellTypeBlanks)
    .EntireRow.Copy Sheets("Archives").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    .EntireRow.Delete
End With
End Sub

 

[Jamm_027]

Thanks jonmo1, works perfectly.

One more question, what if instead of blank cells in column E what if I now wanted to deleted all Non-Blank cells?

 

[Jonmo1]

Try changing
xlCellTypeBlanks
to
xlCellTypeConstants