Inserting Multiple Rows In Very Large Worksheet

[bthurman1220]

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

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

 

[bthurman1220]

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

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

I3

amor

K3

L3

M3

N3

O3

P3

Q3

A4

B4

C4

D4

E4

F4

G4

H4

I4

amor

K4

L4

M4

N4

O4

P4

Q4

A5

B5

C5

D5

E5

F5

G5

H5

I5

dante

K5

L5

M5

N5

O5

P5

Q5

A6

B6

C6

D6

E6

F6

G6

H6

I6

dante

K6

L6

M6

N6

O6

P6

Q6

A7

B7

C7

D7

E7

F7

G7

H7

I7

Men

K7

L7

M7

N7

O7

P7

Q7

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

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

Thank you so much for all your help DanteAmor. Your code will work fine. I was stepping through the code and noticed
that if there were duplicate employee names next to one another then the code was not doing what I wanted. This will not
be an issue because earlier in my code I eliminate all duplicates. Sorry I did not notice this before.

Thank you so much for providing me a solution.

Bob

 

[DanteAmor]

So does it work for you? If it doesn't work, upload an example of your data and the expected result.