#1
|
|||
|
|||
Protect a column from row inserts or deletes
Is it possible to make a column of a worksheet remain static even if I insert or delete rows please?
For example say column A contains the letters from A-Z in rows 1-26. If I delete row 3 then I want it to delete that row from columns B, C, D, E etc. but not from Column A, such that column A still has the letters from 1-26, and all the values in columns B, C, D... for rows 4 onwards have shifted up one row relative to column A. Likewise if I insert a new row after row 3 then I want it to insert a new row in columns B, C, D... but not in column A. To do it manually I have to do the following if I delete a row: - Delete row 3 - Right click on the new cell A3 which has shifted up from A4 and now contains "D" - Select Insert - Change the radio button to select Shift cells down - Re-enter the letter C in cell A3 A similar process if I insert a row. |
#2
|
|||
|
|||
|
#3
|
||||
|
||||
select only the cells you want to delete instead of the entire row (hold down the Ctrl key if you need to select non-contiguous ranges), right-click and choose Delete…:
2022-06-15_163332.png Choose to shift cells up: 2022-06-15_163657.png and you're left with: 2022-06-15_163833.png |
#4
|
|||
|
|||
Thanks for that but it is not much quicker than doing it the original way. Still a pain in the neck and time wasting when doing lots of inserts and deletes. Also risky if there are columns that are off the screen to the right. Risks not inserting or deleting in those columns, ending up with corrupted data.
|
#5
|
||||
|
||||
OK. Assuming that column A is the only column you want to protect, in the attached, is a bit of macro code behind Sheet4 which handles right-clicks on that sheet.
The idea is that you select a range or ranges on the sheet, then you right-click in one of the selected ranges and it will ask you whether you want to adjust all the selected ranges to right across the sheet but excluding column A. Whichever you choose the usual right-click menu will appear, and allow you to do what you intend with the selection. As far as I'm aware, there's no way to protect a column from deletion of some of its rows while allowing the rest of the row to be deleted, hence the my code to adjust the selected cells before operating on them. Last edited by p45cal; 06-16-2022 at 02:00 PM. |
#6
|
|||
|
|||
Thanks for that. Will give it a try.
|
Tags |
freeze, lock |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I un-protect docs I didn't protect? | doctorduncan | Word | 4 | 05-30-2019 11:09 AM |
Ungrouping/grouping to protect document that deletes bookmark rng at contentcontrolonexit procedure | madempress | Word VBA | 7 | 03-14-2018 03:14 PM |
Document that inserts certain info | Jamboy41 | Word | 1 | 11-14-2016 05:03 PM |
a function that deletes all rows with no value in a certain column? | dunndealpr | Excel | 2 | 06-08-2013 06:50 AM |
Tracing Inserts | DonH | Word | 9 | 05-18-2011 02:55 PM |