Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-14-2022, 05:51 PM
pmolsen pmolsen is offline Protect a column from row inserts or deletes Windows 11 Protect a column from row inserts or deletes Office 2016
Novice
Protect a column from row inserts or deletes
 
Join Date: Jun 2022
Posts: 3
pmolsen is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 06-14-2022, 08:07 PM
Logit Logit is offline Protect a column from row inserts or deletes Windows 10 Protect a column from row inserts or deletes Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

How to Protect Specific Columns Using VBA in Excel - ExcelDemy
Reply With Quote
  #3  
Old 06-15-2022, 08:39 AM
p45cal's Avatar
p45cal p45cal is offline Protect a column from row inserts or deletes Windows 10 Protect a column from row inserts or deletes Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
  #4  
Old 06-15-2022, 05:37 PM
pmolsen pmolsen is offline Protect a column from row inserts or deletes Windows 11 Protect a column from row inserts or deletes Office 2016
Novice
Protect a column from row inserts or deletes
 
Join Date: Jun 2022
Posts: 3
pmolsen is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 06-16-2022, 05:35 AM
p45cal's Avatar
p45cal p45cal is offline Protect a column from row inserts or deletes Windows 10 Protect a column from row inserts or deletes Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsm msofficeforums49207.xlsm (15.3 KB, 6 views)

Last edited by p45cal; 06-16-2022 at 02:00 PM.
Reply With Quote
  #6  
Old 06-16-2022, 01:49 PM
pmolsen pmolsen is offline Protect a column from row inserts or deletes Windows 11 Protect a column from row inserts or deletes Office 2016
Novice
Protect a column from row inserts or deletes
 
Join Date: Jun 2022
Posts: 3
pmolsen is on a distinguished road
Default

Thanks for that. Will give it a try.
Reply With Quote
Reply

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
Protect a column from row inserts or deletes Ungrouping/grouping to protect document that deletes bookmark rng at contentcontrolonexit procedure madempress Word VBA 7 03-14-2018 03:14 PM
Protect a column from row inserts or deletes 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
Protect a column from row inserts or deletes Tracing Inserts DonH Word 9 05-18-2011 02:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft