Insert Copied Rows

Hello,

I'm trying to create a macro in the vb editor, that will loop through the rows in a spreadsheet looking for a condition within a column (lets say column H). Once it meets that condition, it should insert a blank row above that row and copy the content into it. Once done it will continue on checking the other rows untill it finishes the spreadsheet. (The total rows are unknown)

Is this possible ?

Thanks,

Originally Posted by Altec101

Hello,

I'm trying to create a macro in the vb editor, that will loop through the rows in a spreadsheet looking for a condition within a column (lets say column H). Once it meets that condition, it should insert a blank row above that row and copy the content into it. Once done it will continue on checking the other rows untill it finishes the spreadsheet. (The total rows are unknown)

Is this possible ?

Thanks,

oh yes!

Assuming that
* your rows start at row nr 1
* the condition which triggers the copy of the current row into a newly inserted row above is "value in column H = 0"

here is the code:

Please Login or Register  to view this content.

Ciao

Thanks for the info.

If the condition in the row in which it was looking for had something like "N8094" once is came in contact with a row that had a starting number containing a letter "N", it would insert a row above it and copy that rows context into the newly insert row, then continue on searching the rest of the spreadsheet untill the end.

The line in your code " If Cells(I, 8) = 0 Then " what is the 8 for? is it a cell referrece, if so I would not know ahead of time which cells contain the condition.

Thanks for the help.

The 8 is for column the 8th column (column h)

You could also use

If Cells(I, "h") = 0

Note:
Alfry's code will stop before the last row you are searching if it has blank cells within the column.

If you have blank cells then try this macro
Change the 0 value in If Cells(iRow, "h").Value = 0 as required

Please Login or Register  to view this content.

Thanks,

I guess i over looked one thing, that the condition within the columns would have different values. I would need the marco to be able to look at the first character and determine if the first character within that value contains a "N".
Example:
Colume H would contain the following data
58477
N8094
N7584
B5863

So the macro should be able to stop at "N8094" for example and insert a row above it and copy that row into the new one, if would again stop at "N7584" and do the same thing. then if would continue on skipping "B5863".

Originally Posted by Altec101

Thanks,

I guess i over looked one thing, that the condition within the columns would have different values. I would need the marco to be able to look at the first character and determine if the first character within that value contains a "N".
Example:
Colume H would contain the following data
58477
N8094
N7584
B5863

So the macro should be able to stop at "N8094" for example and insert a row above it and copy that row into the new one, if would again stop at "N7584" and do the same thing. then if would continue on skipping "B5863".

In that case change the If condition
from :

Please Login or Register  to view this content.

into :

Please Login or Register  to view this content.

ciao