Excel 2007 : Dynamically change number of rows based on value in a cell

Not sure I titled this correctly, but here is what I am trying to do:

I have created a generic template to enter test data from up to 100 samples. There are 100 rows (and 11 parameter fields) which represent each sample.

Sheet1 has the information about the samples, including the number to be tested.
Sheet 2 has the formatted table in which to enter the test data.

I would like to be able to have the number of rows in the test table on Sheet 2 dynamically determined, based on the number of samples entered on Sheet 1. For example, lets say a person is only collecting data from 25 samples. I would like for Sheet 2 to only have 25 rows (instead of 100). All rows must maintain their formatting.

What we are doing now is just manually deleting whatever rows are not used, but it would be nice if we could do this "automagically" instead.

Please let me know if this can be done, or if it is just "wishful thinking."

I have searched and searched, but I can't find the solution. My problem may be just knowing how to correctly phrase the question??

I checked on the status of my post this afternoon and saw 18 views, but no replies

I'm not sure if everyone who has viewed this post so far just don't know the solution, or if they are reading it and laughing because it is something so simple, or it has been answered many times (maybe even in this forum?). I tried quite a few ways to search for the answer in this forum, but so far I came up empty, just like with searching on Google.

Whatever the case may be, if anyone reads this post and does know the solution, if you don't want to just "hand me" the answer I can respect that but please at least tell me a better way to phrase the question and I will look it up myself.

I have tried about <exaggeration>10,000</exaggeration> different ways to phrase the question, but to no avail. All I keep getting are examples of using COUNT and COUNTA, and other random things that aren't relevant.

I don't want to COUNT the number of rows, I want to have X number of rows, determined by whatever number is in another cell. If "5" is in the cell, then there will be 5 rows. If "200" is in the cell, there will be 200 rows.

It seems like this would be something that is pretty simple if you have enough experience with Excel, but so far I haven't found the solution. I have several years of experience using Excel, but this is kicking my butt

I am pretty sure that I am not the first person to ever think of this, so there has to be a solution somewhere.
(If I am the first person to think of this, then that probably means it's not possible )

Here you go... there's a macro button and some pretty simple VBA, you'll be able to figure it out and customize it as needed.

Calculating it automatically would require doing it on update of the sheet, which could cause lots of screen flashing and other stuff, so I figured I'd just throw it into a button for you. If it's a huge issue, I can take another look at it in the morning and swap it how it's run.

Please Login or Register  to view this content.

Thanks, Miraun.

This is pretty much what I was looking for. I figured it would probably have to be done with VBA, but I don't have any experience with VBA at all.

I messed with it a bit and, although it doesn't matter in my case, it appears this only works for up to 140 rows ( I will need no more than 100). If you enter anything more than 140, you still get only 140 rows. (What is it that makes 140 the limit?) Just thought I would share this info in case there came a time where you wanted to use this for more than 140 rows

I do have a couple of questions about it:

1) Can the button be modified? Can it be resized, relabeled, moved?
2) How would this be done automatically? I wouldn't be worried about screen flashes, since there is only one time that this would be used, as it is a template. The person would initially enter the number of samples on sheet1, then they would enter the data in the rows on sheet2. The file would be saved as whatever, and next time, the template would be pulled up and modified again and saved as something else. It wouldn't be "updated" at all.

The 140 current limit is just based upon how far I filled down the sample numbers in Column A. The VBA effectively does a highlight on the entire rows based on a CTRL+Down keystroke, and then unhides them. Then it takes the value in Sample Info A2 and unhides up to that row +1 (for the header). So since there are 140 values in Column A, it goes up to that

1) Yep, button can be made however you want it... Delete it and make a new button if you want. When you right click on the button/symbol/whatnot, you can assign a macro that way.

2) Right now, the code lies within Module 1. I moved the code into the Sample Info worksheet within the VBA Structure, and then changed the run conditions so that every time cell A2 updates, it will run the code. Actually, it appears to be running without any screen flashing or updates. Perhaps I just got better at writing code than last time.

I've also added in a data validation rule on cell A2 to ensure that there are no VBA runtime errors associated with putting in a negative value, and to put in your max of 100 for whenever you decide to alter these examples I'm posting.

Here's the code

Please Login or Register  to view this content.