Auto insert 'x' rows depending on value 'y'

Closed

Excel

nephilim3uk Posts 11 Registration date Monday August 9, 2010 Status Member Last seen October 27, 2010 - Aug 9, 2010 at 02:43 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 17, 2010 at 09:45 PM

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

Excel add rows automatically based on cell value
Insert number of rows based on cell value - Best answers
Add rows based on cell value in excel - Best answers
Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
Pdf and xps add in 2007 - Download - Other
Transfer data from one excel worksheet to another automatically - Guide
How to insert rows in excel automatically based on cell value ✓ - Excel Forum
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum

3 responses

Answer 1 / 3

nephilim3uk Posts 11 Registration date Monday August 9, 2010 Status Member Last seen October 27, 2010 1
Aug 12, 2010 at 06:04 AM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 17, 2010 at 09:45 PM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 9, 2010 at 02:18 PM

See this
https://ccm.net/forum/affich-333050-how-to-repeat-rows-by-x-number-of-times

nephilim3uk Posts 11 Registration date Monday August 9, 2010 Status Member Last seen October 27, 2010 1
Aug 10, 2010 at 03:24 AM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 10, 2010 at 02:25 PM

#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

Similar discussions

Code to move rows into another sheet based on certain values

Insert rows on change of value in Excel table

Macro to insert row based on variable cell value (text)

Excel - To generate unique ID

copy a row from one worksheet to another, based on criteria

Copying data from one Excel sheet to another.

Similar discussions

Subscribe To Our Newsletter!