Auto Un hide Rows

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
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?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 31, 2018
Messages
75
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 31, 2018
Messages
75

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jul 31, 2018
Messages
75
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

Board Regular
Joined
Jul 31, 2018
Messages
75
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,134
Messages
5,599,914
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top