I have this macro to hide rows based on a value in column "A", but my boss keeps deleting the formulas there so i moved the so i referenced the column in the last column so he won't accidentally delete them. problem is, now that column is hidden and the range is dynamic but i don't know how to find a dynamic column range and use it in the macro.
Any help would be much appreciated.
Chris
Please Login or Register to view this content.
Last edited by Code Flunkie; 02-16-2011 at 02:45 PM.
Why can't you protect the sheet with Column A cells Locked, i.e. Unlock only the cells on the sheet where changes are allowed
Remember that any code that calls the protected sheet will have to be unprotected before the code runs and protected on completion
e.g.
Please Login or Register to view this content.
Hope this helps
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
it's my boss' workbook, i don't use it and he tends to screw things up a lot, he also doesn't want it protected.
Tell him to get real, sounds like he'll screw it up regardless of what you do, why not protect it?
That is the a prime purpose of protection, to prevent formulae being accidentally changed.
It need not have a password in case his memory is a bit suspect.
The only reason I can think for his objection is that he wants to change the formula if the result disagrees with him. In that case check the formula.
Don't fix things that aint broke!
he's one of those people that fears change. it's easier to modify the macro than it is to modify some peoples behaviors
Okay, so you have a problem on your hands.
Can you post a sample workbook that shows where your boss makes the changes and where you would like to hide this ability from him, or at least back it up?
The workbook should not contain sensitive data, but nevertheless show the formulae he changes.
Maybe we could fool him into thinking the answer is correct, but somehow I think it is the formula that is wrong.
I think you are misunderstanding the original problem. the formulas are just to determine what rows to hide. they are for the macro to use. I moved the column to the end but when he adds or removes columns the macro will no longer work because the column range is not dynamic. I could put word at the top of that column and find that to reference the column but i'd prefer to hide the column.
sorry for the confusion
Chris
Try this
Please Login or Register to view this content.
In the column of your choice put a unique Header say "SecretColumn", in this case probably your column row 13, you can then hide this column.
Adding columns before this column in the workbook should now be okay.
I don't know how adding rows after row 14 will affect your formula.
What triggers your macro?
Last edited by Marcol; 02-16-2011 at 12:38 PM. Reason: Re-thought post
works beautifully. Thanks a lot, i didn't realize it was so simple to do.
Thanks again,
Chris
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks