Excel VBA - adding rows

Question

I have this code that adds rows where I need them based on information in column C.

Dim lRow As Long
    For sRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row To 3 Step -1
        If Cells(sRow, "C") <> Cells(sRow - 1, "C") Then Rows(sRow).EntireRow.Insert
    Next sRow

The result I get for this is that a row is inserted at row 15 (where my table headings are located), moving those table headings down to row 16. Then another row is inserted between table headings and content at row 17. Then rows are inserted at the desired locations from there on.

How can I change this so that this begins at row 16 and only inserts a new row when the content in column C changes?

Here's what I'm looking for:

and here's what I'm getting:

Each time the script runs, it will push the whole table down one row.

tnavidi · Accepted Answer · 2021-11-24 08:59:01Z

1

If you want to start from row 16, just change the starting point in the for loop

Dim lRow As Long
    For sRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row To 16 Step -1
        If Cells(sRow, "C") <> Cells(sRow - 1, "C") Then Rows(sRow).EntireRow.Insert
    Next sRow

edited Nov 24, 2021 at 8:59

answered Nov 23, 2021 at 14:56

tnavidi

1,4181 gold badge8 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Justin White Over a year ago

Thanks for your input, Tom, but this does not produce the desired effect. I've added what's happening in my original post. This still pushed the table down one row, adds a row after table headings, and then doesn't add any more rows after that.

tnavidi Over a year ago

Ok, please see updated; i've changed the 3 to 16 from your original code.

exceptional exception · Accepted Answer · 2021-11-23 19:45:00Z

0

I think I get it. You're just inserting a row where there's a difference in the 'C' column. Unfortunately, that includes your header. Thy this:

Dim lRow As Long
    For sRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row To 3 Step -1
        If Cells(sRow, "C") <> Cells(sRow - 1, "C") and Cells(sRow - 1, "C").Value <> "Or Ty" Then 
            Rows(sRow).EntireRow.Insert
        End If 
    Next sRow

answered Nov 23, 2021 at 19:45

exceptional exception

5277 silver badges27 bronze badges

Collectives™ on Stack Overflow

Excel VBA - adding rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related