Excels Darange function

[rbasset]

im new to programming in excel, love programming in VB so i didnt think it would be too hard to switch over to excel. The reason I switched is due to work, most of the programs we use are excel based, I was hired on to maintain these programs if any were to `break`.

So too my question, I have to design and write a tracking program, easy enough, but i dont like all the clutter with excel so i hide alot of the cells, so i was wondering how to use the darange function in excel to auto unhide a number of cells once a certain number is reached.

example, say we have cells 1 - 10 unhidden and 6 of them are filled out and from cell 11 - 500 are hidden, but when you fill in the 7th cell excel auto unhides cells 11-15, then when cell 13 gets filled in excel auto unhides cells 16-20 and ect. to cell 500.

If anyone knows how to do this or if theres an easier way to do this please let me know.

[teylyn]

Hello,

there is no DARANGE() function in Excel. Maybe it is a User Defined Function in your environment?

From your description it sounds like you want a Change Event, so that certain rows will be unhidden when a value in a specific cell or row is changed.

On the other hand, I don't quite see why cells would need to be hidden and then unhidden. Why are you hiding them in the first place? Do you have a table with formulas copied down to nirwana?

If so, then why not use an Excel Table instead? It will carry over formatting and formulas to any new row appended or inserted into the table, and you do not have to copy formulas down for future data entry.

cheers,

[rbasset]

basically im looking to make this program loot neat and tidy and only unhide cells when needed, lest clutter less to look at.

[teylyn]

Again: any reason why you are hiding rows?

Please explain your approach to your scenario.

Excel Tables (or Lists in versions prior to 2007) will make pre-filling rows with formulas unnecessary.

cheers

[rbasset]

As stated above I want to hide unused cells until they are needed, this is so users basically dont feel overwhelmed while filling out this tracker and keeping it open all day, so this brings me back to my question, how do i hide rows and automatically unhide 3 or 4 rows when a certain threshold is reached, IE if row 5-10 where hidden and and 1 -2 were filled with random information and the user fills out row 3 with information then automatically rows 5-7 show and so on.


I know theres a command out there to acheive this because i have seen it work, however i cant seem to find out what the command is, i thought it was something like derange and i have googled it and like you said, it is a user defined command but the command that i am looking for is an excel command.

[teylyn]

Can you post a sample file with some dummy data?

[rbasset]

Nvm everybody i figured it out, I will post the code later