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

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.

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.