Help Using xlUP

[business_analyst]

Hello All,

So I have a list located in Column A of a worksheet. This master list is actually comprised of several smaller lists, each separated by an empty cell (e.g. A1:A5, A7:A14, A16:A21) The empties cells here would be A6 and A15. I have included a button on this sheet that I would like to use to delete these mini-lists one at a time starting from the last one. This is the code I have:

Sub Erase_List()
    Do While Sheet5.Cells(Rows.count, 1).End(xlUp).Offset(-1, 0).Value = ""
        Sheet5.Cells(Rows.count, 1).End(xlUp).Value = ""
    Loop
end sub

I am trying to recognize the empty cell between each list in order to allow each section to be deleted one click at a time. The code above is working except for the fact that it is not erasing the cell just below the empty cell between lists. For example if there is data in:

A1:A5 and A7:A14
the above code will delete the data located in cells A8:A14, but leave A7 alone.

Basically I don't know how to get the code to stop exactly on the empty cells between lists.

 

[Smitty]

Instead of a loop I'd try a native approach. Edit-->Goto (Ctrl+G)-->Special-->Blanks, then delete the selected rows.

If it does what you want it's easily recorded.

HTH,