Auto Un hide Rows

[Cooki]

Hi All

Im after some code that will always make sure that there are 10 rows available from the last row with data on and have the formulas and formatting still in.

I have hidden rows and columns to make it look much neater, but if they need to enter more 10 tasks in, will need to unhide, copy everything down and hide again.

So im hoping it would work like, if they enter 5 more rows of data and save the document, another 5 rows would be added to the bottom which would be total of 10 rows.

Columns go across to R.

Is this possible and if so could someone please help?

 

[Joe4]

Are the hidden rows already formatted and contain formulas?
So, is it just a matter of unhiding rows?
Do you only want this to run when they Save or Open the file?
What if they have more than 10 rows of data to enter at once, so they won't be "saving" it until they finish entering all data?

 

[Cooki]

Are the hidden rows already formatted and contain formulas?
So, is it just a matter of unhiding rows?
Do you only want this to run when they Save or Open the file?
What if they have more than 10 rows of data to enter at once, so they won't be "saving" it until they finish entering all data?

No the hidden rows are not formatted
if its possible, would like it to run when its saved, so as you have said in your last question if they have more then 10, they can just save it and more rows will be added.

 

[Joe4]

What columns do you need to copy formatting and formulas down for?
(If there is nothing at all in those rows, I am not sure what the point of hiding them is).

 

[Cooki]

There is formatting on all the columns as i have conditional formatting and there are formulas in columns B,D,E,F,K,L,M,N,O.

Reasons i would like it like that is, its just beat neater with only having limited rows to see and to make sure i could cover any future data input of tasks and i would need to copy the formulas down by few couple hundred, which would mean the file size would be much larger then needed due to the formulas.

With the VB in place the size of the file would only grow when necessary.

I must add im creating them to be locked down so only certain cells can be accessed due to mistakes being made by teams.

 

[Joe4]

There is formatting on all the columns as i have conditional formatting and there are formulas in columns B,D,E,F,K,L,M,N,O.

Excel allows up 16384 columns. Are you saying that you are using all 16384?
What I am asking you for is what the last column you are using is, so we don't have to try to copy all 16384 columns.

With the VB in place the size of the file would only grow when necessary.

OK. Just bear in mind that hiding rows will not reduce the size (memory, I mean, not the number of rows shown) of the file.

 

[Joe4]

Also, in order to determine how many rows to enter, we need to determine which rows have data and which rows are blank.
For rows with data, is there always at least one column that will always be filled out? If so, what is that column? We will use that to determine where our last entry was made.

And I also assume that there won't be any "hidden" rows in the middle of your data (only at the bottom).

 

[Cooki]

Oh no not using that many lol so i have multiple sheets i need to use it for but the largest one is 176 rows.

no im not hiding the rows because i think it will reduce the size of the file. What i said was if i was not to hide the rows i would have to put the formulas in and copy them down to 300 or 400 row to make sure there is enough and doing that and copying the formulas down that far would effect the size of the file.

That is why i thought of this way instead but just dont know how to do it.

 

[Cooki]

Also, in order to determine how many rows to enter, we need to determine which rows have data and which rows are blank.
For rows with data, is there always at least one column that will always be filled out? If so, what is that column? We will use that to determine where our last entry was made.

And I also assume that there won't be any "hidden" rows in the middle of your data (only at the bottom).

All cells in the row apart R will have data in, R is a note cells, so may have data sometimes

 

[Joe4]

Oh no not using that many lol so i have multiple sheets i need to use it for but the largest one is 176 rows.

I am looking for the ending column, not the ending row number.

Actually, if you want it to copy the formulas down too, then we would need to know which column have formulas in them, so we only copy those formula columns, and not ones with hard-coded data (as I assume you do not want those hard-coded entries copied down).