# Insert multiple rows

#### vane0326

##### Well-known Member
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:

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### vane0326

##### Well-known Member
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?

Thanks!

#### tactps

##### Well-known Member
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

##### MrExcel MVP
Or:
Code:
``````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

##### Well-known Member
Much neater Taz. I really need to work on my "for" statements. Loops are inefficient!

#### Tazguy37

##### MrExcel MVP
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

##### Well-known Member
It not quite what I'm looking for.

Here is a example when I used your codes:

#### vane0326

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
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!

Replies
7
Views
190
Replies
1
Views
212
Replies
14
Views
431
Replies
18
Views
287
Replies
0
Views
67

1,195,699
Messages
6,011,183
Members
441,592
Latest member
Vasant bangalore

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back