Solved: Problem with my delete loop

[grichey]

Code Below. I'm trying to delete any row that doesn't have a background of none or white as well as any empty rows. The problem is the way I've looped it deletes a row and jumps to the next to check but by deleting they all move up so if there's two non white or empty rows back to back it skips one.


:banghead:

Option Explicit
Sub RPT1()

Dim counter1 As Integer
Dim stopper1 As Integer
counter1 = 6 'leaves header on report
stopper1 = InputBox("enter stopper", "enter stopper") 'for last row of report
' The above needs to be better

Do
'Below deletes rows with color in background
If Rows(counter1).Interior.ColorIndex = x1None _
Or Rows(counter1).Interior.ColorIndex = 2 _
Or (Application.CountA(Range("A" & counter1).EntireRow) = 0) _
Then
Rows(counter1).EntireRow.Delete

counter1 = counter1 + 1 'loops to next row

Loop Until counter1 = stopper1 'stops when you've reached last row entered at inputbox

MsgBox ("Finished") 'Finish

End Sub

[mae0429]

Just a side note: If that input box is just finding the last row, take a look at the KB articles - there's one for finding the last used row of a sheet.

Actually, now that I think about it, why don't you just work from the bottom up then? Set your counter start at the last row, then increment -1 to get to the "starting" row?

[grichey]

Eh I don't know what the last row is nor do I know how to determine it via vba.

[mae0429]

http://vbaexpress.com/forum/showthread.php?t=9774

Finding the last row :)

[grichey]

Ok. Thanks. Back to my original question about the loop skipping the cases where's there two in a row...

[mae0429]

If you work from the bottom up, you'll bypass that problem:

e.g. You delete row 27 (making the old row 28 the new row 27) and then move up to row 26. Both could have been blank and they'll both get deleted.

Changes you'll have to make:
counter1 = lastrow (whatever variable that may be)
stopper1 = 6 (or whatever the top row is)
counter1 = counter1 - 1

[grichey]

Here's where I am now. The below deletes all the colored rows out but does not delete the totally empty rows.

Ideas? I put a comment in the code where I'm not getting an error nor is it working.
Option Explicit
Sub RPT1()

Dim counter1 As Integer
Dim counter2 As Integer
'counter1 = 6 'leaves header on report
counter1 = InputBox("enter stopper", "enter stopper") 'for last row of report
counter2 = counter1 ' The above needs to be better

Do
'Below deletes rows with color in background
If Not Rows(counter1).Interior.ColorIndex = xlNone _
Or Not Rows(counter1).Interior.ColorIndex = 2 _
And (Application.CountA(Range("A" & counter1).EntireRow)) = 0 _
Then
Rows(counter1).EntireRow.Delete

End If
'The below doesn't seem to work.
If IsEmpty(Range("A" & counter1)) Then
Rows(counter1).EntireRow.Delete
End If

counter1 = counter1 - 1 'loops to next row


Loop Until counter1 = 0 'stops when you've reached last row entered at inputbox

MsgBox ("Finished") 'Finish

End Sub

[grichey]

I figured it out. The blasted Acct software inserted a space I didn't realize into every blank cell. I added TRIM and it's working.

Thanks