How to lock "hidden" Columns for good

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

--
Message posted from http://www.ExcelForum.com

0
6/4/2004 3:25:58 PM
excel 39879 articles. 2 followers. Follow

2 Replies
518 Views

Similar Articles

[PageSpeed] 5

Might pay to give a look at "Custom Views".
-- 

HTH,

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


"Enrique Mahecha >" <<Enrique.Mahecha.17c19j@excelforum-nospam.com> wrote in
message news:Enrique.Mahecha.17c19j@excelforum-nospam.com...
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


---
Message posted from http://www.ExcelForum.com/

0
ragdyer1 (4060)
6/4/2004 7:53:34 PM
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



"Enrique Mahecha <" wrote:
> 
> 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
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/4/2004 11:02:37 PM
Reply:

Similar Artilces:

Hidden rows unhide themselves when formulas are written
I have a worksheet with many hidden rows & columns. However, when I try to write a formula in this worksheet, these hidden rows & columns automatically unhide themselves. Is there any way to disable this? ...

2006 hidden opening bal
I have a hidden opening bal in my file. The instructions on the site to help me are for 2005 and they don't work What's a hidden opening bal? You mean one that's not visible in account settings/details? Did you have a question? "needmohelp" <needmohelp@discussions.microsoft.com> wrote in message news:191E7B74-0C62-44A1-A461-0FC3A27B0C9D@microsoft.com... >I have a hidden opening bal in my file. The instructions on the site to >help > me are for 2005 and they don't work In microsoft.public.money, needmohelp wrote: >I have a hidden opening ...

Can I save formats like hidden columns...
.... and row heights and orders of columns, so I can call them up at a later time? Hi Laurel I would open a new workbook and delete all except for Sheet1 I would create the sheet as I wanted it, with all the various columns, row heights etc., but with no data and save this as a Template with File>Save as>Templates>MySheet Then when required, Insert>Sheet>MySheet -- Regards Roger Govier "Laurel" <FakeMail@Hotmail.com> wrote in message news:evjIVK8JJHA.1156@TK2MSFTNGP04.phx.gbl... > ... and row heights and orders of columns, so I can call them up at ...

Hidden graphics and pictures
I am using Office 2003 and Windows XP Suddenly all my graphics and pictures on all publications I created in Publisher are hidden. They are still there in Word and PowePoint Where did I or what accidentally switch them off. The only thing I did different was that I installed Adobe Acrobat 7 since it happened. Thanks for your kind assistance Hans Look under the View menu. David Bartosik - [MSFT MVP] http://www.publishermvps.com http://www.davidbartosik.com "Hans Studer" <hrstuder@bigpond.com> wrote in message news:tHtke.13094$E7.8999@news-server.bigpond.net.au... &g...

hidden transactions #2
I have a problem with account balances: the balance for checking is way off on the hiome page than in the account register view. When I tried to find out why, I got some greyed out transaction when I went from bill schedule through right click to payee. This transaction cannot be changed or deleted, but in total all such transactions made up the difference in balance. Any suggestions? Oscar -- Elvart In microsoft.public.money, elvigor wrote: >I have a problem with account balances: the balance for checking is way off >on the hiome page than in the account register view. When I ...

Pivot Table
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When editing a PivotTable Field it is possible to hide items from being listed in the drop down box of from being displayed in the table area. However, once I have hidden an item I cannot get that particular field to display all of the items again. <br><br>Any help would be appreciated. Does any of this help? http://office.microsoft.com/en-us/excel/HP051993841033.aspx If not, you need to tell us exactly what you are doing, step-by-step, and what happens when you do it. Cheers On 17/0...

keeping outlook open but hidden.
Is there some way instead of minimize to keep outlook open and checking mail but hidden? -- Office xp Small Biz On Fri, 8 Aug 2003 11:02:45 -0400, "Geek" <geek@geek.com> wrote: >Is there some way instead of minimize to keep outlook open and checking mail >but hidden? I believe this is the best you can do for OL2000 and OL2002: http://support.microsoft.com/default.aspx?scid=kb;en-us;251340 Don't know what they're up to for OL2003. Brendan http://www.outlook-tips.net/Tricks/minimize_tray.htm -- "Geek" <geek@geek.com> wrote in message n...

Hidden column becoming unhidden upon forwarding
Has anyone had any problem with forwarding an Excel 2007 spreadsheet in which a hidden column when forwarded in an email then shows the recipient the hidden information. Can a hidden column, "unhide" on its own? I'd ask several questions: 1) Are they using Excel 2007 to open the workbook? 2) Is there a workbook_open event that runs to unhide columns? -- HTH, Barb Reinhardt "Concerned friend" wrote: > Has anyone had any problem with forwarding an Excel 2007 spreadsheet in which > a hidden column when forwarded in an email then shows ...

Ex 2K Standard Toolbar won't stay hidden
If I deselect an close Excel, the standard toolbar stil pops up next time I open Excel. Formatting toolbar (and others) work correctly. Anyone got any ideas as to where the "secret setting" is that always loads the standard toolbar? Thanks. ...

Can I SAVE AS while preserving hidden-nes of columns?
I have a spread sheet that I would like to turn into a text or Word document, but I want to preserve the hiddennes of the columns? But when I use SAVE AS to a Uniary Text type, .txt, all the columns are visible. I'm very surprised that I can't convert it to a Word document. Maybe I'm missing something somewhere? TIA LAS Copy the visible range to a temporary location (a temporary worksheet). (ctrl-g, special|visible cells only) Then copy from there and paste to MSWord. Laurel wrote: > > I have a spread sheet that I would like to turn into a text or Word &...

hidden pictures
Hi For some reason I can not see any pictures objects or shapes in my publication. I have tried everything from turning view pics on and off to reinstalling the programe. I have no trouble in word pics are fine it's just publisher. I am running xp pro can yo help This is based on Publisher 2000 but it may help you. Look here: View Picture display If you have "Hide pictures" selected, move it to one of the other two options. -- Don Vancouver, USA "Eltel" <Eltel@discussions.microsoft.com> wrote in message news:16110116-DA35-44B3-9265-08A0ADF8E2B1@micro...

Calendar Text Hidden on Web
Publisher 2003. One-page calendar created for print, then saved as web page. Looks great in web preview. Publish to folder on hard drive, then import file and folder with graphics into FrontPage 2003 site. Cuts off text at bottom of some of table cells and part of header. Making cells longer does not work. ...

Trouble with hidden data
Hello, Here's the situiation. I have a sheet with vlookup functions whic displays data from another sheet. Often the data displayed is prett large . When data is entered into cells that vlookup is retrieving the cells automatically expand to show all data entered. When vlooku displays that same data on the other sheet, the cells don't expand. have to manually resize the cell to fix. Is there a way around thi problem?? Thanks -- Message posted from http://www.ExcelForum.com maybe autofit the rows and columns after each calculation? rightclick on the worksheet tab that sho...

what is the alternative for PlotArea.Fill. Fill property is hidden
Chart Object.PlotArea.Fill returns ChartFillFormat object. But in 2007 both the ChartFillFormat object and property Fill of PlotArea are hidden. Actual requirement is like following. .PlotArea.Fill.Visible = True .PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, Degree:=0.3 But the functionality works fine in Office 2007. But according to Excel object model 2007 reference above shown property is hidden, can u please tell me what is hidden. What is the alternative for this property? Can any one please help me. Thanks in advance. Hi, the colors available in...

hidden value in each cell or in each row in Excel
hi, I need to store a two information in one cell with one value as hidden ,kindly reply if any one know the way without creating hidden rows or columns or worksheet The need is to store two information in one cell,with one value in display ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Using comments is one option. >-----Original Message----- >hi, > >I need to store a two information in one cell with one value as hidden >,kindly reply if any one kn...

Why Are Hide Sheet and Unhide Sheet Hidden? (Excel 2007, Windows XP)
I have a workbook file that contains hidden worksheets. I do know the password. What I don't know is how to enter the password so that the hidden worksheets are revealed. When I go to "Cells/Format/Hide & Unhide/Hide Sheet" or "Cells/Format/Hide & Unhide/Unhide Sheet", both menu items are hidden! How to I enter the password to deprotec the file? I am using Excel 2007 on Windows XP. Thank you! sherman sherman <> wrote in news:sl459498luofjq0o88iuv2p04ritks8era@4ax.com: > I have a workbook file that contains hidden worksheets. > I do know the passw...

Hidden or invisible record in account register
I have a transaction that drove me nuts last weekend causing my checking account to be out of balance. It is a downloaded deposit of $2.00 (an ATM reimbursement fee) in my account. The amount is not visible in the account register so I of course added it manually which meant to Money, it is now in there twice, my manual entry plus it's invisible one. The downloaded transaction is not available for viewing in: *The account register, no matter what filtering I have set. *The Advanced Find menu It is visible in: *The category section for ATM/Bank fees window in the lower right *The Payee se...

Hidden accounts
I hid some accounts, and now I can't remember what to do to see them again. How do I get them to show, I have looked in View but there doesn't seem to be an option. Many thanks Eve In microsoft.public.money, emc wrote: >I hid some accounts, and now I can't remember what to do to see them again. >How do I get them to show, I have looked in View but there doesn't seem to >be an option. AccountList->SortAccountListBy->HideClosedAccounts. "Cal Learner-- MVP" <via_newsgroup@please.tnx> wrote in message news:ng7si05m2dt1ah2askimce1erbe72dvl7o@...

hidden/unintentional macros ?
I have a spread sheet that has been collecting data over a 3 year period. When I created it, it had no macros or functions, just a collection of data. Have never had a warning (and I've kept the macro warning setting on through Exel version changes) about the sheet containing macros, as compared with other sheets. I'm currently using Exel XP, and still had no problem, till I reformated a couple of days ago, and now Exel throws a warning every time I open the sheet. No macros are listed in Tools>Macros, so the question is: How does one 'save' the sheet clean, without a...

how can I password hidden columns
I have a workbook which a colleague uses to enter data. The data is then manipulated on the same worksheet, but if these columns arehidden they can easily be unhid. Is there a way of password protecting these consecutive hidden columns, or an alternative please You can hide the columns, then protect the worksheet (tools|protection|protect sheet). But this protection is easily broken. If you have stuff you don't want others to see, don't put in an excel workbook that you share with others. David Allen wrote: > > I have a workbook which a colleague uses to enter data. The...

Hidden Formulas
I have been given an Excel file that has a hidden formula and I can't get it to display. I have tried the various options found on this site (select entire sheet & check format tab for hidden cells, did a search by formula for the cell that is changing, checked named ranges [not sure what to look for on these but didn't see anything unusual], checked VB code for hidden worksheets, etc.) but to no avail. Does anyone have any other suggestions? Thanks!!! Jani If the worksheet is protected and that cell is locked, then you won't be able to see the formula in that cell. If ...

Copying worksheet from hidden instance of Excel
Is it possible to open multiple worksheets in a hidden instance of Excel and copy worksheets to a workbook in the original visible instance? I want to open multiple files and would prefer to not see all the files being opened in the windows task bar? wb was opened in a hidden instance of excel. An error occurs at the ws.copy line. The error is: "Method 'Copy' of object '_Worksheet' failed" Is there another option avoid seeing the files being opened in the task bar? application.screenupdating = false Set wb1 = ActiveWorkbook Set wb = xlApp.Work...

Outlook 2000 HTML mail and other hidden source
I have become quite fed up with Outlook not allowing me to see the source (the WHOLE source) of messages. Additionally, I cannot force Outlook 2000 to NOT display HTML messages. Recently I got an email with a tag <IMG SRC="CID:xxxxxxx"> I don't know where this image is, but if I export it, there's an animation that does not show up. Clearly it is some kind of hidden encoding. I cannot get access to that data whatsoever, unless there's some option I am missing? Please advise. If this is simply a case of That's What Microsoft Assumes You Want, then I am going to...

Displaying results in formula bar when cell is hidden
Is there any way to have the results of a formula appear in the formula bar when the cell is protected and hidden, and sheet protected? I have formulae that retrieve text, but the cells are not wide enough to display all that text, and the formula bar shows nothing (blank) when the cell is protected and hidden, and the sheet is protected. I think my alternative is how excel should work . . . TIA Andy By setting the cell properties to hidden, you hide the data in the formula bar, not in the cell. That's what "hidden" on a protected sheet means. Suggest ...

Hidden Column A Problem
I read the message sent in by ebf kentucky about having his column A hidden in Excel and the solution of unfreezing the frames did not work for m y client. Client is using Office 2007 Excel. It doesn't specify in his email which version he is using so maybe there is something different for 2007. The strange part is when the screen is set to full - the A column appears but when you get the menu bar/ribbon back, the A disappears. Any assistance would be greatly appreciated. Select columns to the left and right of you want to unhide. Go to Home, Cells, Format, Hide & Unhi...