Prevent deletion of data in hidden cells

[Gadgetgav]

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

 

[Snakehips]

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.

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]

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]

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.

 

[Gadgetgav]

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