Using VBA in Excel, how do I clear all data in 1 row that user will select from within a list of rows, and user can first put cursor on any cell in row to choose - all without deleting the actual row?
Posted on 2013-02-04
I will really appreciate your help with this! I do not want to delete an entire Excel row out of the spreadsheet -- I need to keep the rows that are already there, so I only want to clear/erase the contents of one row, when necessary.
This is my current VBA macro:
' DELETESELECTION Macro
Application.ScreenUpdating = False
Application.ScreenUpdating = True
The problem with this macro is the following:
1) The user MUST place the cursor in a cell that is in a particular "ID" column , if they place their cursor anywhere else on that row to be cleared, only part of the row will be erased/cleared, it will not get it all. The user should be able to place their cursor anywhere on that row, and then click the "Delete" macro button (which I already have set up and working).
2) If the user accidentally places the cursor on the header row by mistake (which is locked and protected) and then clicks the "Delete" macro button, because the header cells are locked and the sheet is protected, the program will "break" and bring up the VB code screens and ask to be debugged, which is obviously no good. Is there any kind of Error coding that can be placed at the beginning of the entire macro that will just go to Sub End and nothing will happen in this accidental situation?
3) It would be really nice to be able to "move" all the rows of data left beneath the new empty row (once cleared) up one row so all rows can be contiguous again as they were before a row of data was cleared. However, I would like to keep the same order, not have them sorted -- sorting would put all the blanks at the bottom and that would be desirable, but also rearrange the order of the data, and that would not be desirable.
4) Is there a MsgBox with vbYesNo (or something like that) that can be used whereas when the "Delete" macro button is clicked, a message comes up that tells the user to first place their cursor on the row to be deleted (cleared) -- in case they didn't put their cursor on that row, or anywhere at all -- and then tells them to press Enter to continue.. -- and then lets them click the row they want to clear, and then proceeds with the rest of the macro, clearing that row, moving up the rest of the records below (if any, in case it was the LAST row of data they were clearing), etc.
Thank you! Solving this problem will be very useful!