Select Entire Sheet and Delete all Cells Except Column A

[SHEFFCOURT]

I am stumped. All I want to do is have the user be able to select entire sheet or even rows and hit the delete button to erase all contents of cells except column A. I tried the lock and protect sheet method but that's not what they want. The would like to specifically select the row and delete all contents of row except column A. Can you fine folks help me out? This is almost embarrassing to ask because it seems to be something fairly simple; however, not simple enough for me I suppose.

Thank you in advance for your help.

Sheffcourt

[Pete_UK]

Is the data contiguous across the row (i.e. no blanks) ? If so, you can select cell B1 (for example), then hold down the <Shift> key, press the <End> key once followed by <right-arrow>, then release <Shift> (which will have highlighted all the data across from B1), then press the <Delete> key.

Hope this helps.

Pete

[SHEFFCOURT]

No - I've attached the simple spreadsheet. Column A will never change so they want to use this as a template and for the person to be able to select the entire sheet or select all rows and hit the keyboard delete button and have only column A intact. I don't understand why they can't just select column B and highlight over and delete, but for some reason they would like to be able to do it like the way I described. I have ASAP Utilities and thought it would be a simple process but I can't find anything to help me isolate that one column the way they want.

[Pete_UK]

First of all, select cell A63 and click on the Merge and Centre icon and on Unmerge cells (You should also unmerge any other cells in other sheets). Then, while holding down the CTRL key, press the <End> key and then release <Ctrl> - this will take the cursor to the last-used cell (H469 on sheet HSB & QTs). Hold down the <Shift> key, and do CTRL-Home - this will highlight all the used cells in the sheet - then press the arrow-right button, and then release <Shift>. This has now highlighted all the used cells from B2 onwards. You can then press the <Delete> key to remove the contents.

Hope this helps.

Pete

[SHEFFCOURT]

I'm sorry Pete - I must not be doing a great job of explaining what I need. These guys want to be able to select a row and hit delete and erase all the contents except the cell contents in column A. They don't want to just go to column B and hold shift and highlight over and delete(and I don't understand why they can't just do that) but they want to be able to select the row and hit delete and have all contents on the row deleted except column A. I apologize that I wasnt more clear.

[PeteABC123]

Highlight col A. Format cells, protection, make sure locked is checked.
Choose all other columns, format cells, and unchecked locked in the same manner.
Right click the tab, view code, make sure the properties window is visible in the lower left
In the enable selection field, , change the selection to: 1 - xUnlockedCells Close the VBA explorer window.

Protect the worksheet. This will prevent users from messing with col A.

Hope it helps.
Pete

[SHEFFCOURT]

Hi Pete,

I'm so sorry for late reply. I didn't receive an email showing you had replied. I did use your instructions above. This is almost what I need although they want to be able to select the rows and delete the content except column A. I sent them the one per your instructions and told them I was still working on it but I don't know if it's even possible to do what they are asking at this point.

Do you know how to have the file just like you instructed above but for them to be able to select rows or select entire sheet while keeping Column A content protected? I really appreciate all of your help and time with your replies.

[SHEFFCOURT]

I uploaded the spreadsheet after I applied your instructions. I'm unable to select rows or entire spreadsheet and hit delete which is what they would like to do.Protected_Gen Switch Stock.xls