# Inserting Multiple Rows In Very Large Worksheet

#### bthurman1220

##### New Member
Hoping someone might have some thoughts on optimizing the code below. I am inserting
5 rows each time a new employee is found. Then adding the text for the pay category.

I have done some research and was wondering if "Resize" may be a viable option.

Thanks,

Bob

Code:
``````X = 2

Do Until Cells(X, 1) = ""

Dim vLoop As Long

If Cells(X, 10) <> Cells((X + 1), 10) Then
vLoop = 1

Do While vLoop < 7

Select Case vLoop

Case 1
Cells(X, 18) = "BASE SALARY"
Case 2
Cells(X, 18) = "NON-PROFIT BASED BONUS"
Case 3
Cells(X, 18) = "OTHER"
Case 4
Cells(X, 18) = "OVERTIME"
Case 5
Cells(X, 18) = "PROFIT BASED BONUS"
Case 6
Cells(X, 18) = "TOTAL"
Exit Do

End Select

Range(Cells(X, 1), Cells(X, 17)).Select
Selection.Copy
Rows(X + 1).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

vLoop = vLoop + 1
X = X + 1

Loop

X = X + 1

End If

Loop``````

#### DanteAmor

##### Well-known Member
How many records are in your original database?

Try this

Code:
``````Sub Inserting_Multiple_Rows_1()
Dim i As Long, ant As Variant, arr As Variant
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
arr = Array("BASE SALARY", "NON-PROFIT BASED BONUS", "OTHER", _
"OVERTIME", "PROFIT BASED BONUS", "TOTAL")
For i = Range("J" & Rows.Count).End(xlUp).Row To 2 Step -1
If ant <> Range("J" & i).Value Then
Range("A" & i & ":R" & i).Copy
Range("A" & i + 1 & ":R" & i + UBound(arr)).Insert Shift:=xlDown
Range("R" & i & ":R" & i + UBound(arr)).Value = Application.Transpose(arr)
End If
ant = Range("J" & i).Value
Next
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
End Sub``````

#### bthurman1220

##### New Member
Thanks so much DanteAmor. There are 5,397 records to process.
I tried the code and it did not insert any rows.

#### DanteAmor

##### Well-known Member
According to your macro, employees are in column J and start in row 2.
Isn't that how your data is?
Can you put a sample of how your data is?
If the workbook contains confidential information, you could replace it with generic data.

Did you modify something in the macro?

#### bthurman1220

##### New Member
DanteAmor - I have a test file but not sure how to post it here. you are correct in that
the employee numbers start in Column "J" and on row 2.

Last edited:

#### DanteAmor

##### Well-known Member
Do you have formulas in column J or values?
Check that you don't have cells with blaks after the last employee in column J.

Perform a test with this data.
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:21.86px;" /><col style="width:20.91px;" /><col style="width:20.91px;" /><col style="width:21.86px;" /><col style="width:19.96px;" /><col style="width:19.96px;" /><col style="width:21.86px;" /><col style="width:21.86px;" /><col style="width:17.11px;" /><col style="width:40.87px;" /><col style="width:20.91px;" /><col style="width:19.01px;" /><col style="width:24.71px;" /><col style="width:22.81px;" /><col style="width:22.81px;" /><col style="width:20.91px;" /><col style="width:22.81px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A2</td><td >B2</td><td >C2</td><td >D2</td><td >E2</td><td >F2</td><td >G2</td><td >H2</td><td >I2</td><td >amor</td><td >K2</td><td >L2</td><td >M2</td><td >N2</td><td >O2</td><td >P2</td><td >Q2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A3</td><td >B3</td><td >C3</td><td >D3</td><td >E3</td><td >F3</td><td >G3</td><td >H3</td><td >I3</td><td >amor</td><td >K3</td><td >L3</td><td >M3</td><td >N3</td><td >O3</td><td >P3</td><td >Q3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A4</td><td >B4</td><td >C4</td><td >D4</td><td >E4</td><td >F4</td><td >G4</td><td >H4</td><td >I4</td><td >amor</td><td >K4</td><td >L4</td><td >M4</td><td >N4</td><td >O4</td><td >P4</td><td >Q4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A5</td><td >B5</td><td >C5</td><td >D5</td><td >E5</td><td >F5</td><td >G5</td><td >H5</td><td >I5</td><td >dante</td><td >K5</td><td >L5</td><td >M5</td><td >N5</td><td >O5</td><td >P5</td><td >Q5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >A6</td><td >B6</td><td >C6</td><td >D6</td><td >E6</td><td >F6</td><td >G6</td><td >H6</td><td >I6</td><td >dante</td><td >K6</td><td >L6</td><td >M6</td><td >N6</td><td >O6</td><td >P6</td><td >Q6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >A7</td><td >B7</td><td >C7</td><td >D7</td><td >E7</td><td >F7</td><td >G7</td><td >H7</td><td >I7</td><td >Men</td><td >K7</td><td >L7</td><td >M7</td><td >N7</td><td >O7</td><td >P7</td><td >Q7</td></tr></table>

#### bthurman1220

##### New Member
Do you have formulas in column J or values?
Check that you don't have cells with blaks after the last employee in column J.

Perform a test with this data.
 A B C D E F G H I J K L M N O P Q 1 A B C D E F G H I J K L M N O P Q 2 A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 3 A3 B3 C3 D3 E3 F3 G3 H3 I3 amor K3 L3 M3 N3 O3 P3 Q3 4 A4 B4 C4 D4 E4 F4 G4 H4 I4 amor K4 L4 M4 N4 O4 P4 Q4 5 A5 B5 C5 D5 E5 F5 G5 H5 I5 dante K5 L5 M5 N5 O5 P5 Q5 6 A6 B6 C6 D6 E6 F6 G6 H6 I6 dante K6 L6 M6 N6 O6 P6 Q6 7 A7 B7 C7 D7 E7 F7 G7 H7 I7 Men K7 L7 M7 N7 O7 P7 Q7

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:21.86px;"><col style="width:20.91px;"><col style="width:20.91px;"><col style="width:21.86px;"><col style="width:19.96px;"><col style="width:19.96px;"><col style="width:21.86px;"><col style="width:21.86px;"><col style="width:17.11px;"><col style="width:40.87px;"><col style="width:20.91px;"><col style="width:19.01px;"><col style="width:24.71px;"><col style="width:22.81px;"><col style="width:22.81px;"><col style="width:20.91px;"><col style="width:22.81px;"></colgroup><tbody>
</tbody>

Here is the starting data:
 A B C D E F G H I J K l m n o p q r s A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 A3 B3 C3 D3 E3 F3 G3 H3 I2 amor K3 L3 M3 N3 O3 P3 Q3 A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 A5 B5 C5 D5 E5 F5 G5 H5 I2 dante K5 L5 M5 N5 O5 P5 Q5 A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7

<colgroup><col width="64" span="19" style="width:48pt"> </colgroup><tbody>
</tbody>

Here is the results. The code I used was exactly what you posted.

 A B C D E F G H I J K l m n o p q r s A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 A3 B3 C3 D3 E3 F3 G3 H3 I2 amor K3 L3 M3 N3 O3 P3 Q3 A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 BASE SALARY A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 NON-PROFIT BASED BONUS A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 OTHER A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 OVERTIME A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 PROFIT BASED BONUS A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 TOTAL A5 B5 C5 D5 E5 F5 G5 H5 I2 dante K5 L5 M5 N5 O5 P5 Q5 A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 BASE SALARY A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 NON-PROFIT BASED BONUS A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 OTHER A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 OVERTIME A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 PROFIT BASED BONUS A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 TOTAL A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 BASE SALARY A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 NON-PROFIT BASED BONUS A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 OTHER A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 OVERTIME A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 PROFIT BASED BONUS A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 TOTAL

<colgroup><col width="64" span="20" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks again. You have no idea how much I appreciate this.

#### DanteAmor

##### Well-known Member
So, does my code work for you?
Did you test my code with your real data?
Improved runtime?

#### bthurman1220

##### New Member
So, does my code work for you?
Did you test my code with your real data?
Improved runtime?
Code is still not working perfectly. Below is what I expect to see after running the code
and you can see from my results above this is not happening.

 A B C D E F G H I J K l m n o p q r s A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 BASE SALARY A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 NON-PROFIT BASED BONUS A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 OTHER A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 OVERTIME A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 PROFIT BASED BONUS A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 TOTAL A3 B3 C3 D3 E3 F3 G3 H3 I2 amor K3 L3 M3 N3 O3 P3 Q3 BASE SALARY A3 B3 C3 D3 E3 F3 G3 H3 I2 amor K3 L3 M3 N3 O3 P3 Q3 NON-PROFIT BASED BONUS A3 B3 C3 D3 E3 F3 G3 H3 I2 amor K3 L3 M3 N3 O3 P3 Q3 OTHER A3 B3 C3 D3 E3 F3 G3 H3 I2 amor K3 L3 M3 N3 O3 P3 Q3 OVERTIME A3 B3 C3 D3 E3 F3 G3 H3 I2 amor K3 L3 M3 N3 O3 P3 Q3 PROFIT BASED BONUS A3 B3 C3 D3 E3 F3 G3 H3 I2 amor K3 L3 M3 N3 O3 P3 Q3 TOTAL A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 BASE SALARY A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 NON-PROFIT BASED BONUS A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 OTHER A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 OVERTIME A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 PROFIT BASED BONUS A4 B4 C4 D4 E4 F4 G4 H4 I2 amor K4 L4 M4 N4 O4 P4 Q4 TOTAL A5 B5 C5 D5 E5 F5 G5 H5 I2 dante K5 L5 M5 N5 O5 P5 Q5 BASE SALARY A5 B5 C5 D5 E5 F5 G5 H5 I2 dante K5 L5 M5 N5 O5 P5 Q5 NON-PROFIT BASED BONUS A5 B5 C5 D5 E5 F5 G5 H5 I2 dante K5 L5 M5 N5 O5 P5 Q5 OTHER A5 B5 C5 D5 E5 F5 G5 H5 I2 dante K5 L5 M5 N5 O5 P5 Q5 OVERTIME A5 B5 C5 D5 E5 F5 G5 H5 I2 dante K5 L5 M5 N5 O5 P5 Q5 PROFIT BASED BONUS A5 B5 C5 D5 E5 F5 G5 H5 I2 dante K5 L5 M5 N5 O5 P5 Q5 TOTAL A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 BASE SALARY A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 NON-PROFIT BASED BONUS A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 OTHER A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 OVERTIME A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 PROFIT BASED BONUS A6 B6 C6 D6 E6 F6 G6 H6 I2 dante K6 L6 M6 N6 O6 P6 Q6 TOTAL A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 BASE SALARY A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 NON-PROFIT BASED BONUS A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 OTHER A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 OVERTIME A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 PROFIT BASED BONUS A7 B7 C7 D7 E7 F7 G7 H7 I2 men K7 L7 M7 N7 O7 P7 Q7 TOTAL

<colgroup><col width="64" span="20" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks again.

#### DanteAmor

##### Well-known Member
Does this example work for you?

Perform a test with this data.
 A B C D E F G H I J K L M N O P Q 1 A B C D E F G H I J K L M N O P Q 2 A2 B2 C2 D2 E2 F2 G2 H2 I2 amor K2 L2 M2 N2 O2 P2 Q2 3 A3 B3 C3 D3 E3 F3 G3 H3 I3 amor K3 L3 M3 N3 O3 P3 Q3 4 A4 B4 C4 D4 E4 F4 G4 H4 I4 amor K4 L4 M4 N4 O4 P4 Q4 5 A5 B5 C5 D5 E5 F5 G5 H5 I5 dante K5 L5 M5 N5 O5 P5 Q5 6 A6 B6 C6 D6 E6 F6 G6 H6 I6 dante K6 L6 M6 N6 O6 P6 Q6 7 A7 B7 C7 D7 E7 F7 G7 H7 I7 Men K7 L7 M7 N7 O7 P7 Q7

<tbody>
</tbody>

If you run the macro more than once the table grows and grows.

Only run the test once and review the results.

------------------------------------------------------------------------------------
If it is not what you need then upload a file to the cloud with 2 sheets on sheet1 you put your original table and on sheet2 you put the expected result.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Last edited: