Delete cell and shift cell up

[mridzuan]

I have data in cells A1:A4. I want if cell A2 is deleted using the button delete, the cell will automatically shift up. Can a macro do this?

[MSP77079]

Yes. You want an event procedure. Do you want this apply to:
1. just one worksheet?
2. or every worksheet in one workbook?
3. or every workbook open at the time?

1 & 2 are pretty simple. You write a worksheet_change event macro either on the worksheet's code window (case 1), or the workbook's code window (case 2). http://www.cpearson.com/excel/events.htm

3 is more difficult, as it requires "application events", which do not come "built-in". You would need to write a ClassModule to create application events. http://www.cpearson.com/excel/AppEvent.htm

[mridzuan]

I need it just for one worksheet.

[MSP77079]

Just one column or all columns on that sheet?

[mridzuan]

For a range of columns in the worksheet

[MSP77079]

OK, since you won't tell me, and I can't seem to drag it out of you ,,, here is the code if the "range of columns" you happen to want are B:J (2 through 10).

If you happen to want a different range of columns, change the numbers in the first 2 IF statements.

Please Login or Register  to view this content.

To find the right place to put this code, select the tab for the worksheet you want this to work for; right-click, from the context menu select "View Code" and paste this where the cursor is flashing.

[mridzuan]

I'm sorry if you misunderstood me. Let me explain it in full. In Sheet1, I have data in cells A1:A10. What I wanted is if I were to delete cell A2 using the "Delete" button on the keyboard, cell A3 will shift up.

[MSP77079]

So, if I ask this question again:

Just one column or all columns on that sheet?

Your answer is "just column A", right?

In that case, the code is:

Please Login or Register  to view this content.

Or, if I am still confused ... draw me a picture. I am slow sometimes (I mean it!)

[mridzuan]

Thanks. It works just fine for the whole column. What needs to be done if I want the Macro to work only on a range of cells like I mentioned before?

[MSP77079]

So ,,, I told you I can be slow at times.

cells A1:A10 right?

Please Login or Register  to view this content.

Do you want the cells from A11 down to stay in place? That would require a few more lines of code.

[MSP77079]

I noticed a slight problem with the code I posted earlier. Namely, if the next cell happends to be also be blank, it never stops until to gets to a non-blank cell.

Code below fixed that. And, if you want rows 11+ to stay in place, remove the tic-mark (') from the row that is green.

Please Login or Register  to view this content.

[mridzuan]

It works perfectly as I needed. A million thanks to you.

[MSP77079]

Thanks for the feedback!