Insert multiple rows

[vane0326]

I would like to know is there a visual code for this?
Look at collumn (A) if the text its by itself and collumn (B) if the number one shows up insert one row BUT if number one is more then insert three rows.

Here is a better example
(A) (B)
COPE & DRAG #1 1
insert one Row
COPE & DRAG #2 2
insert one row

But If it looks like this
COPE & DRAG #2 2
COPE & DRAG #2
Insert row
insert row
insert row

Here is a before:

 

[vane0326]

Here is after:

If you notice look at collumn (A) it says
HUNTER-1 ( LARGE 20X24 ) and 3 rows were insert because in collumn (B)
the numbers are different it says 1 and 2 I have it seperated.
is there a visual basic code for this?
Please Help

Thanks!

 

[tactps]

Not the most efficient method, but try this:
Sub insert()
Range("A3").Select
Do Until ActiveCell.Text = "" And ActiveCell.Offset(1, 0).Text = "" And ActiveCell.Offset(4, 0).Text = ""

If ActiveCell.Text = ActiveCell.Offset(-1, 0).Text And ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value Then
ActiveCell.Offset(1, 0).Select
Else
If ActiveCell.Text <> ActiveCell.Offset(-1, 0).Text And ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value Then
ActiveCell.EntireRow.insert
ActiveCell.Offset(2, 0).Select
Else
If ActiveCell.Offset(0, 1).Value <> ActiveCell.Offset(-1, 1).Value Then
ActiveCell.EntireRow.insert
ActiveCell.EntireRow.insert
ActiveCell.EntireRow.insert
ActiveCell.Offset(4, 0).Select
End If
End If
End If
Loop
End Sub

 

[Tazguy37]

Or:

Sub insRows()
Dim i As Long

    lastRow = Range("A65536").End(xlUp).Row
    For i = lastRow To 3 Step -1
        If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
            If Cells(i - 1, 2) = 1 Then
                Rows(i).EntireRow.Insert
            Else
                If Cells(i - 1, 2) > 1 Then Rows(i & ":" & i + 2).EntireRow.Insert
            End If
        End If
    Next i
End Sub

Hope that helps!

 

[tactps]

Much neater Taz. I really need to work on my "for" statements. Loops are inefficient!

 

[Tazguy37]

That's not always true. I have this thing about using for loops, especially in Excel. Must have something to do with everything being numbered (1,2,3... etc.).

Anyhow, with a sheet like this, where the data's in a nice compact list, I find that a for loop works better. If there were certain times in the loop that you want to stop, then a do or a while loop makes more sense. But this example could certainly be converted to a do or a while loop.

 

[vane0326]

It not quite what I'm looking for.

Here is a example when I used your codes:

 

[vane0326]

Now here what i'm looking for:
If you notice it insert 3 rows when there are multiple shifts in collumn (B)

Is there a code could do this?

 

[Tazguy37]

Now I'm somewhat confused.

In the example you just posted, between rows 9 and 13, three rows were inserted (I'm assuming because of the change in Column B from 1 to 2).

But between rows 21 and 23, only one row was inserted. Column A does not change in either of these case, only Column B, in the same way, from 1 to 2.

Why insert one row the first time, and three rows the second?

 

[vane0326]

A Note When you see (A) & (B) those are collumns And the 1 & 2 are in collumn (B)

Yes your right when you said:

Tazguy37
In the example you just posted, between rows 9 and 13, three rows were inserted (I'm assuming because of the change in Column B from 1 to 2).


And when you asked the question:

Tazguy37
But between rows 21 and 23, only one row was inserted. Column A does not change in either of these case, only Column B, in the same way, from 1 to 2.

The answer is because if its more than one then enter 3 rows.
Example:

(A) (B)
HUNTER-2 ( LARGE 20X24 ) 1
Insert row

BUT!

If it looks like this:

(A) (B)
HUNTER-2 ( LARGE 20X24 ) 1
HUNTER-2 ( LARGE 20X24 ) 1
insert row
insert row
insert row

OR

(A) (B)
HUNTER-2 ( LARGE 20X24 ) 1
HUNTER-2 ( LARGE 20X24 ) 1
HUNTER-2 ( LARGE 20X24 ) 1
HUNTER-2 ( LARGE 20X24 ) 1
Insert row
insert row
insert row

OR

(A) (B)
HUNTER-2 ( LARGE 20X24 ) 2
HUNTER-2 ( LARGE 20X24 ) 2
Insert row
Insert row
Insert row

Now if it looks like this the code should do this:

(A) (B)
HUNTER-2 ( LARGE 20X24 ) 1
Insert Row
HUNTER-2 ( LARGE 20X24 ) 2
Insert Row
HUNTER-2 ( LARGE 20X24 ) 3
Insert Row
HUNTER-2 ( LARGE 20X24 ) 4

You notice it only insert one row Because in collumn (B) the the numbers change But if they were multiple and exactly the same then it would insert three rows.

If you have a code for this, it would surly help me.

Thanks!