How to lock "hidden" Columns for good

  • Thread starter Thread starter Enrique Mahecha
  • Start date Start date
E

Enrique Mahecha

Post Preview:
Hi Friends


I have been using Exel for a long time. I use exel heavily from time t
time and this is one of those times. I am not an expert but I kno
exel, the common stuff, pretty good I think. There is something tha
always drives me crazy! in fact, I just finished talking to one of ou
Exel help desk people and could not find an obvious solution to m
problem that is why I coming to you.

My problem is this: how do I LOCK the hidden columns from a particula
sheet, so that when I need to Format/Row or Column/Autofit, the stupi
"hidden" columns won't pop up back on the sheet!.... This is so basic
I can't believe there isn't a simple solution to this. Now I am workin
on a 30 column and many rows worksheet. as usual I need to hide a bunc
of columns (bot not delete them), filter, bent and shape the data i
many ways without compromising the integrity of the sheet... But ever
time I Format/Row/Autofit the table, (either by highlighting the entir
worksheet or simply by highlighting all the columns) the stupi
"hidden" columns pop back up on!

MY ROUTINE: I hide columns and rows I don't want and leave just th
data I want to play with (filter, bend and shape in any way I want to)

I use Format / Column or Row/Autofit...to compact the table,
- then I readjust the width of columns just slightly to my liking -an
then I would like to tell Exel: ok LOCK the data as is now!.(SET I
UP).. but still allow me to play with what's left on the worksheet, bu
always adjusting the table's width and height to what set it before
Mind you, I still want to have all the options, such as adding mor
columns or rows if I choose to, and again be able to, filter data
change width and height...etc, but I always want the table to regres
back to the ORIGINAL set up (that is, adjust the row's height an
column's width to the way I LOCKED it). I have the feeling thi
involves some programming and if it's not too complicated, I would lik
to try it.


Thanks a million for your help and I hope I made my point clearly.


Rico


P.S. I would be happy just If could LOCK the hidden data and keep i
from popping back on the sheet, but if I can get more the bette
 
Might pay to give a look at "Custom Views".
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message Post Preview:
Hi Friends


I have been using Exel for a long time. I use exel heavily from time to
time and this is one of those times. I am not an expert but I know
exel, the common stuff, pretty good I think. There is something that
always drives me crazy! in fact, I just finished talking to one of our
Exel help desk people and could not find an obvious solution to my
problem that is why I coming to you.

My problem is this: how do I LOCK the hidden columns from a particular
sheet, so that when I need to Format/Row or Column/Autofit, the stupid
"hidden" columns won't pop up back on the sheet!.... This is so basic,
I can't believe there isn't a simple solution to this. Now I am working
on a 30 column and many rows worksheet. as usual I need to hide a bunch
of columns (bot not delete them), filter, bent and shape the data in
many ways without compromising the integrity of the sheet... But every
time I Format/Row/Autofit the table, (either by highlighting the entire
worksheet or simply by highlighting all the columns) the stupid
"hidden" columns pop back up on!

MY ROUTINE: I hide columns and rows I don't want and leave just the
data I want to play with (filter, bend and shape in any way I want to),

I use Format / Column or Row/Autofit...to compact the table,
- then I readjust the width of columns just slightly to my liking -and
then I would like to tell Exel: ok LOCK the data as is now!.(SET IT
UP).. but still allow me to play with what's left on the worksheet, but
always adjusting the table's width and height to what set it before.
Mind you, I still want to have all the options, such as adding more
columns or rows if I choose to, and again be able to, filter data,
change width and height...etc, but I always want the table to regress
back to the ORIGINAL set up (that is, adjust the row's height and
column's width to the way I LOCKED it). I have the feeling this
involves some programming and if it's not too complicated, I would like
to try it.


Thanks a million for your help and I hope I made my point clearly.


Rico


P.S. I would be happy just If could LOCK the hidden data and keep it
from popping back on the sheet, but if I can get more the better
 
One extra step might make life simpler.

Hit ctrl-a to select all the cells (or select all your columns)

Then hit
Edit|goto (or F5 or ctrl-g)
then click special
select visible cells only.

Then adjust your column widths.

Or...
Create a range name that refers to row 1 of each column that should be hidden.
Then adjust your columnwidths
then select that range.
then format|column|hide
 
Thank you RagDyer for your suggestions. I will give it a try and se
what happens.


Ric
 
RagDyer, David, than you for your help so far.



No, I am still not getting it.


Both of your solutions are basically related to "views." Basically yes
after adjusting and hiding columns, you can name the final view with
NAME. And yes, when you enter that view name in the "Name Box" <Uppe
left> the view comes on the screen - great so far.


Now, I start working on that same view or worksheet(ws). I wide
certain columns, enter more data, etc, but then the view does not g
back to the original view (with its special column and rows dimensions
but simply, stays just as it looked when I finished working with it.
It does go back to it's original shape. Follow me? think of
rubber-band, you can stretch it, play around with it, but when yo
finish, it goes back to its original shape! - THAT IS WHAT I WANT!
<smile>... As it is right now, sure I can go back to a view - stretc
the band to a particular size (change column widths), but then stay
like that "stretched" (without shrinking back!).


MORE SPECIFIC: After "saving" the "Perfect view", you want to go bac
to the WS and do some more work on it and in the process you probabl
change the width or height of Columns and/or Rows. Now you want to loo
what you have done. At this point you want to go back to the "perfec
view,"so your enter the name in the NAME-BOX, and the view, shoul
adust the table just as specified in the "perfect View." See m
point?... I don't want to keep adjusting the table over and over t
the "perfect View" - I do want to "stretch" the rubber-band, but at th
end, I want the table to "shrink" back to the settings o
"Perfect-view."

As to the second part of Dave's suggestions.... this I have not trie
because I really don't understand it (create range name that refer
to row 1.... etc)


--------------------------------

You know, when you think of it, this seem like such a basic thing
can't imagine people could do without this!... all you really want i
for the table to OBEY your commands. I you hide columns or rows, THE
SHOULD STAY HIDDEN until you tell the table otherwise!!!... and if yo
choose columns A, B, C and D to be of different widths..... the
should obey you and keep those properties until you decide to chang
them. If fact there should be a command called "Save View settings,
(just like a file) where the data is updated but DIMENSIONS remain th
same <smile> ..... .. IT'S GOT to be something like this.. it's got to
:)

I thought this is what a view would do, but there must be something
am not doing right, because my views are not dynamic (like the rubbe
band), the columns stay open at their new width.



TIA


Ric
 
From what you are saying here, it *can* do what you are requesting.

I don't believe that you're using the "Custom View" feature to it's fullest
capabilities.

Check out this old post, and post back with any questions

http://tinyurl.com/3cwqa
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message RagDyer, David, than you for your help so far.



No, I am still not getting it.


Both of your solutions are basically related to "views." Basically yes,
after adjusting and hiding columns, you can name the final view with a
NAME. And yes, when you enter that view name in the "Name Box" <Upper
left> the view comes on the screen - great so far.


Now, I start working on that same view or worksheet(ws). I widen
certain columns, enter more data, etc, but then the view does not go
back to the original view (with its special column and rows dimensions)
but simply, stays just as it looked when I finished working with it.
It does go back to it's original shape. Follow me? think of a
rubber-band, you can stretch it, play around with it, but when you
finish, it goes back to its original shape! - THAT IS WHAT I WANT!
<smile>... As it is right now, sure I can go back to a view - stretch
the band to a particular size (change column widths), but then stays
like that "stretched" (without shrinking back!).


MORE SPECIFIC: After "saving" the "Perfect view", you want to go back
to the WS and do some more work on it and in the process you probably
change the width or height of Columns and/or Rows. Now you want to look
what you have done. At this point you want to go back to the "perfect
view,"so your enter the name in the NAME-BOX, and the view, should
adust the table just as specified in the "perfect View." See my
point?... I don't want to keep adjusting the table over and over to
the "perfect View" - I do want to "stretch" the rubber-band, but at the
end, I want the table to "shrink" back to the settings of
"Perfect-view."

As to the second part of Dave's suggestions.... this I have not tried
because I really don't understand it (create range name that refers
to row 1.... etc)


--------------------------------

You know, when you think of it, this seem like such a basic thing I
can't imagine people could do without this!... all you really want is
for the table to OBEY your commands. I you hide columns or rows, THEY
SHOULD STAY HIDDEN until you tell the table otherwise!!!... and if you
choose columns A, B, C and D to be of different widths..... they
should obey you and keep those properties until you decide to change
them. If fact there should be a command called "Save View settings,"
(just like a file) where the data is updated but DIMENSIONS remain the
same <smile> ..... .. IT'S GOT to be something like this.. it's got to!
:)

I thought this is what a view would do, but there must be something I
am not doing right, because my views are not dynamic (like the rubber
band), the columns stay open at their new width.



TIA


Rico
 
RagDyer, Thanks again for your help.


Still experimenting and still not getting it. Question: HOW do yo
"save" a "view"? - Maybe that is where the problem is...

1- Suppose I have a four column WS: A,B,C,D. All columns are 2 inche
wide and I want to save this as my original-view. As you sai
somewhere, I SAVE the exel file to preserve the ORIGINAL under fil
name: Original.xls. I also save the first view under "Original."

2- I hide Column A and "save" the view under "3Columns" (3 column
left)
to save it: Views/Custom Views/Add (3Columns)/OK.
(I assumed I have "saved" the views as I clicked the "ok" bottom.

3- Then, I widen Column B to 4 inches and save the view under "4InCol
(3 columns left)

4- Then, I hide Columns C & D, so the only Column left is B, the 4 inc
Column and save it under "BColumn."

Ok, there you go: 4 views. By choosing each of this views I should se
exactly what was on the screen the moment I clicked "OK" for tha
particular view, right? - I believe that is the theory... BUT someho
it does not work like this for me.

5- Say I click on view "4InCol" (you should see 3 columns: B 4 inches
C & D 2 inches wide) and then I widen B to 6 inches!... but, I hav
**NOT SAVED anything!.** (just like a file), therefore the next time
call for the same view, B Column should still be 4 inches wide! - Bu
that is not what happens.... B Column is now 6 inches wide.... That i
why the issue of "saving" views becomes critical.


I hope I am not confusing you, but after so many emails, I am trying t
see if we can get something out of all this.

I you could just indicate to me HOW do you SAVE a view, that remain
unchanged until you SAVE a change, that would help me.


Again, TIA for your valuable time.



Ric
 
First of all, I hope that you've placed the "CustomViews" window on your
toolbar.

Next, I assume you have an existing WB with data and formulas.
I advise that you save this as your "orig" view.

Your saving procedure sounds correct:
<View> <CustomViews> <Add>
Enter a name in the "Name" box, then <OK>.

You will *immediately* then see this name displayed in the "Views" box on
your toolbar, informing you that the "save" was completed.

Next, hide some columns, and change the color of the grid lines (makes a
particular view easy to follow).

Once again go through the same "save" procedure, and let's name this simply
"Tst1" (no quotes).

Your "Views" box now displays this "Tst1" name.

Expand the views box to now see *both* of your views.

Click on one, then the other, to toggle back and forth between the views.

You now know that everything is working correctly.

Now, in your "Tst1" view, hide a column or two.
Then change to "Orig", and immediately toggle back to "Tst1".

You'll see the "rubber band" has sprung back, and the columns that you just
hid, are back in view again.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message RagDyer, Thanks again for your help.


Still experimenting and still not getting it. Question: HOW do you
"save" a "view"? - Maybe that is where the problem is...

1- Suppose I have a four column WS: A,B,C,D. All columns are 2 inches
wide and I want to save this as my original-view. As you said
somewhere, I SAVE the exel file to preserve the ORIGINAL under file
name: Original.xls. I also save the first view under "Original."

2- I hide Column A and "save" the view under "3Columns" (3 columns
left)
to save it: Views/Custom Views/Add (3Columns)/OK.
(I assumed I have "saved" the views as I clicked the "ok" bottom.

3- Then, I widen Column B to 4 inches and save the view under "4InCol"
(3 columns left)

4- Then, I hide Columns C & D, so the only Column left is B, the 4 inch
Column and save it under "BColumn."

Ok, there you go: 4 views. By choosing each of this views I should see
exactly what was on the screen the moment I clicked "OK" for that
particular view, right? - I believe that is the theory... BUT somehow
it does not work like this for me.

5- Say I click on view "4InCol" (you should see 3 columns: B 4 inches,
C & D 2 inches wide) and then I widen B to 6 inches!... but, I have
**NOT SAVED anything!.** (just like a file), therefore the next time I
call for the same view, B Column should still be 4 inches wide! - But
that is not what happens.... B Column is now 6 inches wide.... That is
why the issue of "saving" views becomes critical.


I hope I am not confusing you, but after so many emails, I am trying to
see if we can get something out of all this.

I you could just indicate to me HOW do you SAVE a view, that remains
unchanged until you SAVE a change, that would help me.


Again, TIA for your valuable time.



Rico
 
RagDyer, I just want to save you some time and maybe help someone wit
the same problem.


I found the solution... just what I wanted - like a rubber band! :)
a MACRO. That's all. Since a Macro is just a small program tha
reproduces a series of commands, you can do this in number of ways.

The key is in choosing the Columns c-a-r-e-f-u-l-l-y.

You can start by telling exel to HIDE colums <A,D,F... etc> [Hy holdin
<CTRL> down and clicking on the colums to make a group, then hide al
of them.]

Then again, you can choose the rest of the columns you want to AUTOFI
- making sure not to mix them with the HIDDEN colums, (you leave
those alone!)

You can even do more stuff. Say you want column M and N 20 inches wid
and in RED text. No problem. Just press CTRL down, click on the Colum
and /format/width/20/ok/Format/cell/font/RED!

You can even add new columns and after running the MACRO, it keeps th
WS, just as you want it. Now, if you add too many columns, you coul
RECORD a brand new MACRO or simply Add another one, taking care of th
New Colums you added after recording the first Macro. I am positive
you can even connect several Macros, etc etc.


Thank you much for your time.


Ric
 
Back
Top