Excel - Protect formulas in hidden columns

[tmcrae]

I have a spreadsheet with hidden columns that contain formulas. In the unhidden columns are data that I want to clear. But when I select the entire block and press "DELETE", it also clears the formulas in the hidden columns.

What is the best way to prevent this from happening (other than individually selecting the columns to clear rather than selecting the entire block)?

Thanks in advance.

 

[wec43wec]

Try the following:

Highlight the target area

Select:
Edit
Go to
Special
Visible Cells

 

[tmcrae]

Thanks, your solution works perfectly.

I was really looking for a way to mark or protect the actual columns with the formulas in them from being deleted, but this will work very well in the meantime.

As a follow up, is there a way to set "Visible Columns Only" as a default on a spreadsheet, or is this the only way to do it?

Thanks again.

 

[KenWright]

So select the entire sheet and do Format / cells / Protection - untick 'locked'. then select the columns you will hide and do the reverse and lock them and then hide them. Then do Tools / Protection / Protect sheet and you have what you wanted.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------