Excel VBA Delete Rows

Question

I am trying to create a program that will delete or create rows based on what a user puts in a certain row. For example, if the user puts in 3, but there are only 2 rows, then it will insert 1 row. If there were 5 rows, it would delete rows 4 and 5. It seems like it should be an easy code, but I am having the hardest time having it actually delete/create the rows that I want it to. My code is as follows:

Sheets("Summary").Select

x = Cells(29, 3).Value
i = 7

Sheets("Weighted I").Select

Do Until Cells(i, 1).Value = "TOTAL"
    i = i + 1
Loop

i = i - 7
If i > x Then   
    dlt = i - x + 7

    For cnt = 7 To dlt
        Rows(cnt).EntireRow.Delete
        cnt = cnt + 1
    Next    
ElseIf i < x Then
    crt = x - i + 7

    For cnt = 7 To dlt
        Rows(cnt).EntireRow.Insert
        cnt = cnt + 1
    Next
End If

Iron Man · Accepted Answer · 2016-01-27 19:53:33Z

3

This is a common problem when deleting rows. Imagine you are moving through your for loop one row at a time and deleting:

For cnt = 7 To dlt
    Rows(cnt).EntireRow.Delete
    cnt = cnt + 1
Next

You are on row 7 and you delete it. This shifts all of your rows up. Row 8 is now Row 7. You then increase your cnt variable by 1 (to 8) and delete row 8. But you missed row 7, which was row 8... it's crazy bananas.

Instead, change your for loop to work backwards:

For cnt = dlt to 7 step -1
    Rows(cnt).EntireRow.Delete
    cnt = cnt - 1
Next

This way the row shifting doesn't affect your cnt.

edited Jan 27, 2016 at 19:53

Iron Man

8491 gold badge13 silver badges28 bronze badges

answered Jan 27, 2016 at 16:34

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

1 Comment

broncos15 Over a year ago

JNevill, that worked perfectly. Thank you, I knew it just had something to do with my logic in the code. Thank you so much for the help!

Collectives™ on Stack Overflow

Excel VBA Delete Rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related