Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Auto insert 'x' rows depending on value 'y' [Closed]

Report
Posts
11
Registration date
Monday August 9, 2010
Status
Member
Last seen
October 27, 2010
 -
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hi,
I have an excell spreadsheet that contains a table. I want to be able to insert a given number of rows dependant on the value inserted to a particular cell. So if value is '3' , it will insert 3 rows, if value is '6' it will insert 6 rows and so on.
Any suggestions welcome. I have limited experience in using macros and almost none with vb

many thanks

See more
Related:

3 replies

Answer 1 / 3
Posts
11
Registration date
Monday August 9, 2010
Status
Member
Last seen
October 27, 2010
 1
Hi

I still cant figure this out, even with teh sample data. My spreadsheet is as follows;

Column A
Invoice Date - input by user

Column B
Invoice Number - Input by user

Column C
Invoice Total - Input by user

Column D
Number of Budget Centres invoice is split accross - Value 1-60 input by user

Column E
Value of invoice per budget centre - automatically calculate C divided by D

Columns F - O
Additional data - input by user

I want the sheet to automatically insert the number of rows indicated in column D minus 1 (the existing row becomes row 1) Also the value calculated in Column E should be copied into the new rows.

Suggestions to date appreciated, any further advice greatly received.

regards
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Answer 2 / 3
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
See this
https://ccm.net/forum/affich-333050-how-to-repeat-rows-by-x-number-of-times
Report
Posts
11
Registration date
Monday August 9, 2010
Status
Member
Last seen
October 27, 2010
 1
Hi

Thanks for this, looks like it could do the trick.
Forgive my ignorance though, can I check the following;
1- Do I put this code on the VB editor for the sheet (right click sheet, view code) ?
2- Do I need to add or change anything to the code ?

thanks again
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
#1 Once you start VBE, You would add a new module and paste the code in that newly added module

#2 If you sheet looks like the sample data there, then nothing needs to be changed
Answer 3 / 3
Posts
11
Registration date
Monday August 9, 2010
Status
Member
Last seen
October 27, 2010
 1
Hi
Ask a question