Insert Rows Based on Cell Value VBA

Hi all,

I am looking to write some VBA code that will look for a numerical value in column "O" and insert that amount of rows but minus 1. (So if 3 appears, it will insert 2 rows).

I also need to then copy the information from the rest of the row and paste the values. All the cells contain formula, so I just need to values to be pasted here.

I am completely new to VBA, but quite an advanced Excel user.

Insert rows Where? Under the value in O, over the value of O, at the top of the page?
Copy from "the rest of the row" - Column A through LastColumn minus Column O, or the cells after column O going to, or something else entirely?

Hi Jomili,

Insert the required number of cells under the target row.

The whole row is fine to be copied, I just wrote it a bizarre way!

See if this does the trick. In the example we're searching for the value of 131 in column O. I tried to comment the code but feel free to ask if you don't understand something.

Please Login or Register  to view this content.

I've tried the code but it doesn't seem to work for me.

I have attached the workbook I am working on. In the third sheet 'GRP' it is pulling information through and calculating that a panel would need to be cut a set number of times (column O) from a sheet. I am trying to get it add the rows below based on this value, so in the case of the first row, it would be two extra rows, and copy the data down. Does that make sense?

Yes it does. Now that I have an example to work with I should have something for you pretty quickly.

Cheers pal.

I'd love to learn how to do VBA, I just don't know where or how to start!

Looking forward to seeing what magic you can perform.


A

I don't know about "amazing" but I think I might have a solution for you. The Attachment contains 2 GRP sheets, one before, one after the code. If the result is the way you want it, the code in the workbook should do it for you.
If you have tons of rows let me know and I can incorporate some tweaks to speed it up for you.

Please Login or Register  to view this content.

Whoops, just noticed you had some formulas in there, that in the new rows were copied as values. If you want to keep those as formulas, in this section of the code change ".Value" to ".FormulaR1C1". I haven't tested it but it should work just fine for you.

Please Login or Register  to view this content.

Originally Posted by jomili

Whoops, just noticed you had some formulas in there, that in the new rows were copied as values. If you want to keep those as formulas, in this section of the code change ".Value" to ".FormulaR1C1". I haven't tested it but it should work just fine for you.

Please Login or Register  to view this content.

This is absolutely perfect!

Copying values is exactly what I needed it do so that it stays the same as the row above.

Genius! Proper genius!

Thank you buddy!

I don't think I've ever been called a "Proper genius" before; smartypants, knowitall, jerk, yes, but not proper genius. Thanks!

Originally Posted by jomili

I don't think I've ever been called a "Proper genius" before; smartypants, knowitall, jerk, yes, but not proper genius. Thanks!

Hi Jomili,

I've been running the code that you wrote and I have noticed that after running the macro (even using the test data in the file I sent you) it moves a lot of the data around. Do you know if there is a way to keep it in the same order, but insert the rows below?

Not sure what you mean by "moving the data around". The column order seems the same, the only change I see is that, for instance, the first line (Row 2), with a value of 3 in the Qty To Fill column, occupies 3 lines afterward (Rows 2-4). Because of the insertion of the 2 additional lines, the next data set, with a 6 in the Qty to Fill column, begins on Row 5 AFTER the macro, but on Row 3 BEFORE the macro. Is it the row numbers that you're talking about changing? If so, how do you want the result to appear?

Originally Posted by jomili

Not sure what you mean by "moving the data around". The column order seems the same, the only change I see is that, for instance, the first line (Row 2), with a value of 3 in the Qty To Fill column, occupies 3 lines afterward (Rows 2-4). Because of the insertion of the 2 additional lines, the next data set, with a 6 in the Qty to Fill column, begins on Row 5 AFTER the macro, but on Row 3 BEFORE the macro. Is it the row numbers that you're talking about changing? If so, how do you want the result to appear?

Sorry Jomili, I don't think I explained it very well.

I have attached a couple of screenshots to show you what the data looks like before running the macro, and afterwards. I hope it makes more sense that my random vague explanation.

I'm not sure why that's happening. In the sample I attached earlier we DON'T get a result like that: did you make any changes to the macro, or to the sample data? 'd need to see what data you're running the macro on that's causing a result like what you're seeing. Can you provide another sample?

Originally Posted by jomili

I'm not sure why that's happening. In the sample I attached earlier we DON'T get a result like that: did you make any changes to the macro, or to the sample data? 'd need to see what data you're running the macro on that's causing a result like what you're seeing. Can you provide another sample?

Hi,

I have attached the sample data that was shown in the screen shots.

Hope it helps.
Ash

I see the problem(s); working on it now.

Okay, I think we've got it done here. This also might be easier code to follow. I've tried to comment all the steps. Let me know if that gets it or more tweaking is needed.

Please Login or Register  to view this content.

It works like a dream!

Thank you!

Is there a way to make the macro run on a specific sheet, even if it is not the active sheet?

Sure thing, all we need to do is activate whichever sheet you want. In the code, where it says "With Activesheet", change it like shown below:

Please Login or Register  to view this content.

That will dump you out on the Grp (2) sheet when you're all done. If you want to return to the sheet you were originally on when you started the macro you'd need to capture that sheet name before you Activate the sheet you want to run the macro on, then go back to it at the end. See below.

Please Login or Register  to view this content.

I had a few range references coded wrong, which was why I Activated the sheet we worked on in the last code. I've corrected it now, so there's no need to activate any sheet using the code below:

Please Login or Register  to view this content.