Inserting Multiple Rows In Very Large Worksheet

bthurman1220

New Member
Joined
Oct 24, 2019
Messages
11
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
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
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
Joined
Oct 24, 2019
Messages
11
Thanks so much DanteAmor. There are 5,397 records to process.
I tried the code and it did not insert any rows.
Thanks again for your help.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
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? :confused:
 

bthurman1220

New Member
Joined
Oct 24, 2019
Messages
11
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
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
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
Joined
Oct 24, 2019
Messages
11
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.
ABCDEFGHIJKLMNOPQ
1ABCDEFGHIJKLMNOPQ
2A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2
3A3B3C3D3E3F3G3H3I3amorK3L3M3N3O3P3Q3
4A4B4C4D4E4F4G4H4I4amorK4L4M4N4O4P4Q4
5A5B5C5D5E5F5G5H5I5danteK5L5M5N5O5P5Q5
6A6B6C6D6E6F6G6H6I6danteK6L6M6N6O6P6Q6
7A7B7C7D7E7F7G7H7I7MenK7L7M7N7O7P7Q7

<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:
ABCDEFGHIJKlmnopqrs
A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2
A3B3C3D3E3F3G3H3I2amorK3L3M3N3O3P3Q3
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4
A5B5C5D5E5F5G5H5I2danteK5L5M5N5O5P5Q5
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7

<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.

ABCDEFGHIJKlmnopqrs
A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2
A3B3C3D3E3F3G3H3I2amorK3L3M3N3O3P3Q3
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4BASE SALARY
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4NON-PROFIT BASED BONUS
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4OTHER
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4OVERTIME
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4PROFIT BASED BONUS
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4TOTAL
A5B5C5D5E5F5G5H5I2danteK5L5M5N5O5P5Q5
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6BASE SALARY
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6NON-PROFIT BASED BONUS
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6OTHER
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6OVERTIME
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6PROFIT BASED BONUS
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6TOTAL
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7BASE SALARY
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7NON-PROFIT BASED BONUS
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7OTHER
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7OVERTIME
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7PROFIT BASED BONUS
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7TOTAL

<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
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
So, does my code work for you?
Did you test my code with your real data?
Improved runtime?
 

bthurman1220

New Member
Joined
Oct 24, 2019
Messages
11
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.

ABCDEFGHIJKlmnopqrs
A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2BASE SALARY
A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2NON-PROFIT BASED BONUS
A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2OTHER
A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2OVERTIME
A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2PROFIT BASED BONUS
A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2TOTAL
A3B3C3D3E3F3G3H3I2amorK3L3M3N3O3P3Q3BASE SALARY
A3B3C3D3E3F3G3H3I2amorK3L3M3N3O3P3Q3NON-PROFIT BASED BONUS
A3B3C3D3E3F3G3H3I2amorK3L3M3N3O3P3Q3OTHER
A3B3C3D3E3F3G3H3I2amorK3L3M3N3O3P3Q3OVERTIME
A3B3C3D3E3F3G3H3I2amorK3L3M3N3O3P3Q3PROFIT BASED BONUS
A3B3C3D3E3F3G3H3I2amorK3L3M3N3O3P3Q3TOTAL
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4BASE SALARY
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4NON-PROFIT BASED BONUS
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4OTHER
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4OVERTIME
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4PROFIT BASED BONUS
A4B4C4D4E4F4G4H4I2amorK4L4M4N4O4P4Q4TOTAL
A5B5C5D5E5F5G5H5I2danteK5L5M5N5O5P5Q5BASE SALARY
A5B5C5D5E5F5G5H5I2danteK5L5M5N5O5P5Q5NON-PROFIT BASED BONUS
A5B5C5D5E5F5G5H5I2danteK5L5M5N5O5P5Q5OTHER
A5B5C5D5E5F5G5H5I2danteK5L5M5N5O5P5Q5OVERTIME
A5B5C5D5E5F5G5H5I2danteK5L5M5N5O5P5Q5PROFIT BASED BONUS
A5B5C5D5E5F5G5H5I2danteK5L5M5N5O5P5Q5TOTAL
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6BASE SALARY
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6NON-PROFIT BASED BONUS
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6OTHER
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6OVERTIME
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6PROFIT BASED BONUS
A6B6C6D6E6F6G6H6I2danteK6L6M6N6O6P6Q6TOTAL
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7BASE SALARY
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7NON-PROFIT BASED BONUS
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7OTHER
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7OVERTIME
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7PROFIT BASED BONUS
A7B7C7D7E7F7G7H7I2menK7L7M7N7O7P7Q7TOTAL

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


Thanks again.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
Does this example work for you?

Perform a test with this data.
ABCDEFGHIJKLMNOPQ
1ABCDEFGHIJKLMNOPQ
2A2B2C2D2E2F2G2H2I2amorK2L2M2N2O2P2Q2
3A3B3C3D3E3F3G3H3I3amorK3L3M3N3O3P3Q3
4A4B4C4D4E4F4G4H4I4amorK4L4M4N4O4P4Q4
5A5B5C5D5E5F5G5H5I5danteK5L5M5N5O5P5Q5
6A6B6C6D6E6F6G6H6I6danteK6L6M6N6O6P6Q6
7A7B7C7D7E7F7G7H7I7MenK7L7M7N7O7P7Q7

<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:

Forum statistics

Threads
1,078,275
Messages
5,339,213
Members
399,286
Latest member
ossa

Some videos you may like

This Week's Hot Topics

Top