Prevent deletion of data in hidden cells

Gadgetgav

New Member
Joined
Aug 26, 2009
Messages
29
Hi Guys,

I have a spreadsheet which has some columns that are a duplicate of the column before them (i.e. B1=A1 etc.) and other columns with formulas in them

I am training someone else to run the report that uses this sheet and want to limit human error by automating as much of the data entry as possible.

My plan is to put the formula "=A1" in cell B1 etc. then hide column B the problem is that each week they need to drag the rows down by 7 (1 for each day), to autofill the formulas, then delete the data from those new lines to enter in the latest week's data.

Is there a way of protecting the hidden columns (B etc.) so that the formulas will be dragged down when they add new rows but won't be deleted when they delete the old data (if they do this by highlighting multiple cells in a row, including the hidden column (i.e. A-C with B hidden))?

Thanks All
Gav
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,396
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Gadgetgav,

Maybe try something like below that will extend the formulas only by 7 rows.
There will be a slight issue if your rightmost column(s) is hidden as the last column count (lc) would then be incorrect. Worry about that and find alternative if it's actually the case.
As is, assuming you have no sheet protection.
Code:
Sub Extend_Formulas()
lr = cells(Rows.Count, "A").End(xlUp).Row
lc = cells(1, Columns.Count).End(xlToLeft).Column  'assumes last column will not be hidden?
Application.ScreenUpdating = False
For c = 1 To lc
If cells(1, c).HasFormula Then
  Set Rng = cells(lr, c)
  Set AFRng = Rng.Resize(8, 1)
    Rng.AutoFill Destination:=AFRng, Type:=xlFillDefault
    End If
Next c
    
End Sub

Hope that helps.
 

Gadgetgav

New Member
Joined
Aug 26, 2009
Messages
29
Hi Tony,

Thank you for that but I'm trying to avoid macros (for now at least) due to the nature of the report and how it gets distributed (sorry, I should've said that in the original post).

I will definitely bear that code in mind for future reference though, if I do work out a way of using macros without them getting blocked by the client's email system.

Gav
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,396
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Gav,

Then maybe do it manually like....
Select your last full row of data and drag it down by 7 rows. Use the whole row so that it captures any hidden columns that may be at the extreme right of your data.
Deselect those 8 rows then select only the 7 new rows.
In ribbon click Find & Select and then click Constants in the dropdown menu.

Right click one of the selected cells and click Clear Contents.

That should clear all new unhidden cells and leave all new hidden cells with their formulas.
 
Solution

Gadgetgav

New Member
Joined
Aug 26, 2009
Messages
29
Gav,

Then maybe do it manually like....
Select your last full row of data and drag it down by 7 rows. Use the whole row so that it captures any hidden columns that may be at the extreme right of your data.
Deselect those 8 rows then select only the 7 new rows.
In ribbon click Find & Select and then click Constants in the dropdown menu.

Right click one of the selected cells and click Clear Contents.

That should clear all new unhidden cells and leave all new hidden cells with their formulas.
That works in principle but I am trying to make things simpler for a fairly basic excel user to be able to cover my absences and possibly take over from me in the future. I'm pretty sure based on their skill level it would just be easier to get them to type the number in twice.
Thanks anyway
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,260
Messages
5,769,091
Members
425,516
Latest member
Melzebu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top