Does a Do-While loop break a For Each-Next Loop?

Question

I'm trying to make sure that a range has visible rows before I act on those visible rows (delete them) because if I try to act on a filtered range when there aren't any visible rows there, I'll get an error.

Dim lastrow As Integer
Dim ws as Worksheet

For Each ws In ActiveWorkbook.Worksheets
        ws.Activate

        ws.Range("$A$8").Select
        Selection.End(xlDown).Select
        lastrow = ActiveCell.Row

        'DELETE PART CLASSES ROWS
        ActiveSheet.Range("$O$7:$O$" & lastrow & "").AutoFilter Field:=1, Criteria1:= _
        Array("CONS", "MISC", "PFG", "PRT", "TOTE", "="), _
        Operator:=xlFilterValues

        Range("$A$8").Select
        Do
            If ActiveCell.SpecialCells(xlCellTypeVisible) Then
                ActiveSheet.Range("$O$8:$O$" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.DELETE
                Exit Do
            End If
        While ActiveCell.Row < lastrow

        Range("$O$8").AutoFilter Field:=1

Next

The code block was working fine until I added the Do-If-While nested loops. I now get the Compile error: Next without For

What am I doing wrong?

Thank you.

Doesn't your `Do While` need a `Loop`? `Do .. IF .. End if .. Loop While...` — Raystafarian, Jun 11 '15 at 16:43
Also you should really avoid using `.Select` and `.ActiveCell` as it can cause tons of reference problems... [See this post for more](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Chrismas007, Jun 11 '15 at 18:08

score 2 · Accepted Answer · 2015-06-11T18:14:45.130

It appears your do/while loop should be written like this:

Do
    If ActiveCell.SpecialCells(xlCellTypeVisible) Then
        ActiveSheet.Range("$O$8:$O$" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.DELETE
        Exit Do
    End If
Loop While ActiveCell.Row < lastrow

If you wish to have a plain while loop that always evaluates the condition, rather than not evaluating it the first time, you need to write it like this:

Do While ActiveCell.Row < lastrow
    If ActiveCell.SpecialCells(xlCellTypeVisible) Then
        ActiveSheet.Range("$O$8:$O$" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.DELETE
        Exit Do
    End If
Loop

score 0 · Answer 2 · answered Jun 11 '15 at 18:38

Instead of deleting row by row, delete them all. You're going about it in a really complicated way. All you have to do is search for the first unfiltered row. I do this by searching for the first empty cell below the header in the very last column (which works unless you use every single column available, which is really, really unlikely.)

Sub deleteUnfiltered()
'
    Dim ws As Worksheet

    headerRow = 1

    For Each ws In ActiveWorkbook.Worksheets

        ws.Activate
        lastCol = Columns.Count
        firstRow = Columns(lastCol).Find(What:="", After:=Cells(headerRow, lastCol), LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

        lastRow = Cells(Rows.Count, headerRow).End(xlUp).Row
        If lastRow <= headerRow Then lastRow = firstRow

        Rows(firstRow & ":" & lastRow).Delete Shift:=xlUp

    Next

End Sub

I'd think that looping row by row would be much slower than doing them all in one shot.

Does a Do-While loop break a For Each-Next Loop?

2 Answers2