Row deletion

**mtfd** · 01-12-2011, 04:52 PM

Disclaimer: I have no idea what I am doing with VB; I work in the C family.

Currently we have excel workbooks in which specific rows need to be deleted (skip 2, delete 3).

I spaghetti'd this together and it seems to be working:

Sub Change()
    Dim Cnt As Integer
    Dim DeleteFlag As Integer
    
    DeleteFlag = 1
    
    For Cnt = 1 To 505
        BgRange = "A" & Cnt & ":BB" & Cnt
            If DeleteFlag = 5 Then
                DeleteFlag = 1
                Range(BgRange).Interior.Color = RGB(255, 0, 0)
                'Range("A" & Cnt).EntireRow.Delete
            Else
                If DeleteFlag > 2 Then
                    Range(BgRange).Interior.Color = RGB(255, 0, 0)
                    'Range("A" & Cnt).EntireRow.Delete
                End If
                DeleteFlag = DeleteFlag + 1
            End If
    Next

End Sub

When run, this works as I want it to.

However, when flipping statement comments (eg, comment out the BG change and uncommenting the row deletion), it gives me wildly inaccurate deletions.

Anyone know what I am doing wrong?

**TMS** · 01-12-2011, 05:33 PM

Delete from the bottom up :-)

For Cnt = 505 to 1 step -1

You might need to adjust your logic ... maybe, step -5

Regards

**mtfd** · 01-13-2011, 11:15 AM

Originally Posted by TMShucks

Delete from the bottom up :-)

For Cnt = 505 to 1 step -1

You might need to adjust your logic ... maybe, step -5

Regards

Excellent, thank you. I didn't realize you could do that, maybe VB isn't so horrible after all.

For anyone who is interested, this is the working code. (I hate googling & finding these posts with no solution posted at the end.)

Sub Change()
    Dim Row As Integer
    Dim DeleteFlag As Integer

    DeleteFlag = 1
    
    For Row = 505 To 1 Step -1
        bgrange = "A" & Row & ":BB" & Row

        If DeleteFlag > 5 Then
            DeleteFlag = 1
        End If

        If DeleteFlag = 4 Or DeleteFlag = 5 Then
            DeleteFlag = DeleteFlag + 1
        Else
            DeleteFlag = DeleteFlag + 1
            'Cells(Row, 2).Value = DeleteFlag
            'Range(bgrange).Interior.Color = RGB(255, 0, 0)
            Range("A" & Row).EntireRow.Delete
        End If
    Next

End Sub

Row deletion

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Row deletion

Re: Row deletion

Re: Row deletion

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions