Functional Equivalent Of xlSheetVeryHidden For Rows?

[(PeteCresswell)]

I'm creating a rather intricate and interrelated .XLS via VBA
code and the .XLS itself has quite a bit of VBA code in it.

User has decided that they do not want to see certain rows on the
final product.

Problem with just doing a Worksheet(rownum).Delete is that a
deleted row may be referenced in the VBA code. I could change
the VBA code, but now we're talking about a major rewrite and a
lot of man hours.

Just hiding it would simplify the change greatly.

The zinger is that the user does not want anybody to be able to
un-hide the hidden rows.

My first fallback position would be to just clear the hidden
rows, but that might surface problems too. My second fallback
would be to change the font color of said rows so that the info
is not visible even if the row is.

So, bottom line, is there a technique to effectively hide
discontiguous rows in such a way that a casual user cannot
un-hide them?

 

[Dave Peterson]

Not really.

About the best you can do is hide the row and then protect the worksheet.

But worksheet protection is easily broken and the interested user can use a
formula like:

=a1
to see the value in A1 even if row 1 is hidden.

 

[(PeteCresswell)]

Per Dave Peterson:

Not really.

About the best you can do is hide the row and then protect the worksheet.

But worksheet protection is easily broken and the interested user can use a
formula like:

=a1

Thanks for confirming what I already suspected.

Oh well...

What I wound up doing was:

With Worksheet.Rows(xxx)
.Hidden = True
.Clear
End With

This seems to float everybody's boat - and the code isn't
tripping over anything.

 

[Stan Brown]

User has decided that they do not want to see certain rows on the
final product.

Problem with just doing a Worksheet(rownum).Delete is that a
deleted row may be referenced in the VBA code. I could change
the VBA code, but now we're talking about a major rewrite and a
lot of man hours.

Just hiding it would simplify the change greatly.

The zinger is that the user does not want anybody to be able to
un-hide the hidden rows.

Why not move those rows to a sheet and make that sheet veryhidden?