VBA Add rows or split cell according to another cell value

[josekr80]

Hello,
I hope someone can help me.
I'm trying to find a way to split a cell to rows. The no. of rows should be the same as a value inserted on another cell.
I tried to add a picture, hope it will work.
Thanks in advance.

Excel.jpg

[sintek]

Hello josekr80

Rule No 1 in Excel...Avoid merged cells at all costs they just cause havoc with VBA...
Secondly...A sample upload means so so much more than images...We have no test file to make us of...

The no. of rows should be the same as a value inserted on another cell.

What cell what sheet etc etc...
In your sample upload give a Before|After scenario so that we understand what is required...

[bulina2k]

Hi there josekr80

try the attached file and tell me what you think.


LE:
I had no other idea but to write code that follows the normal excel behavior. Here is the code I wrote:

Please Login or Register  to view this content.

The code is triggered as any cell is changed in the sheet.

[AliGW]

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand.

EDIT: Thanks for adding extra details.

[josekr80]

Hey, it works very good.
I had only 1 issue:when you insert the value 2,3,4 ... the number of rows changes accordingly, that is good. But if you insert 3, and try to change it to 1 or zero, the number of rows stays the same. I mean, you can't bring it back to the original number of rows.
And one more thing, I tried by myself to add more rows on the top of the sheet, for header, etc... and it affects the code. I couldn't change the code by my self. Is there any chance to get a file that the table starts on row number 6? So I''l have some space on the top?
Thanks a lot in advance.

[bulina2k]

Hi there Josekr,

I modified the code in the NEW.xlsm file that you attached. Now the code takes into consideration the extra column '# of Samples' that you put there.
Now the value 1 takes away the extra rows.
The 2 lines at the end of the code, need the first cell address of the table. (A7) so if you add/subtract extra rows, please modify accordingly.
Inserting rows does not damage the code but inserting columns, especially before the 'no. of bad parts' column, yes it makes the code useless.
So, some requirements for this code to work would be:
- don't insert columns before the triggering column (that is the F column now)
- the Part ID must be filled before using this code.


The modified code is:

Please Login or Register  to view this content.

[josekr80]

Thank you very much for your time, especially for bulina2K that solved this issue.
It will help me a lot, and I'm learning a lot here.
Have a great day / night.