Deleting Rows via For-Loop (within a multi Area Range)

Question

Usually, I would delete Rows using a For-Loop with row-numbers by working backward, which would look similar to this:

For rowIndex = 10 to 1 Step (-1)
    'delete Row with current row index
    next rowIndex

How do I do this while cycling through a specified cell range? The following code obviously won't work (it will skip a cell/row in the next iteration when the delete command has been executed, check here or here):

Dim someRange as Range
'Note: someRange might be a multi area range (multiple unadjoined cells) within one sheet
Dim singleCell as Range
For each singleCell In SomeRange.Cells
    'check for some condition e.g. based (but not limited to) the singleCell's value
    If condition = True then
        singleCell.EntireRow.Delete
        'Note: the deletion has to be done before the next loop-iteration starts
               'Unfortunately, this makes solutions like working with Union unfeasible
        End If
    Next singleCell

Has anyone an idea how to make the second code block work without switching to a "Backward-Row-Number-Loop" (see first code block)? Is there a way to somehow "reset" the singleCell-pointer/counter to a new value so the loop does not skip the next cell/row? Or any other alternative (like make the Range-Loop work backward etc.)? Any hint for a solution would be appreciated, a code snipped as well but if necessary I can do without.

I have to avoid adding the undesired row into an array (range etc.) and deleting the whole array after the loop is done. Unfortunately as a condition for the implementation the row has to be deleted before the next cell is evaluated.

Essentially there might be a case where two cell which rows ought to be deleted will be in the same row. In this case the 2nd cell will be deleted before it's even checked, that's desired behaviour. The solution does not have to but should be expandable to include this case.

EDIT: Another solution I'm thinking about is reversing the range before doing the For-Loop (see here), so far I haven't tried it out though.

Answer 1

Try this code, please:

Sub TestdeleteRows()
 Dim sh As Worksheet, someRange As Range, singleCell As Range, rngDel As Range
 Set sh = ActiveSheet ' use what sheet you need
 Set someRange = sh.Range("A2:A20")
 For Each singleCell In someRange.Cells
    If singleCell.Value = Empty Then
        If rngDel Is Nothing Then
            Set rngDel = singleCell
        Else
            Set rngDel = Union(rngDel, singleCell)
        End If
    End If
 Next
 rngDel.EntireRow.Delete xlUp
End Sub

It deletes EntireRow for empty cells. It can be conditioned for everything needed.

Answer 2

'If you are deleting rows downwards based on a criteria, the row number of the next row to be evaluated will be the same as the current deleted row number.

'Please try following.

Sub test()

Dim someRange As Range
'Note: someRange might be a multi area range (multiple unadjoined cells) 
within one sheet
Set someRange = Range("B1:B18")
J = someRange.Rows.Count
Dim i As Long
i = 1
Dim singleCell As Long
For singleCell = 1 To someRange.Rows.Count
'check for some condition
If someRange.Cells(singleCell, 1) = 0 Then
    someRange.Cells(singleCell, 1).EntireRow.Delete
    'now as the row is deleted you should reset the value of singleCell
    singleCell = singleCell - 1
    'Note: the deletion has to be done before the next loop-iteration starts
    End If


    i = i + 1
    If i = J Then Exit For
    'someRange.Rows.Count will be the max number of rows to be evaluated
    'otherwise there will be infinite loop and macro wont stop

Next


End Sub

Answer 3

To strictly follow your stated desire to to top down deletion, and to allow for possible multiple hits on one row, and to allow for non-contiguous ranges, I'd suggest not using a for loop, but rather a Do Loop over the Areas and Rows in your someRange, and a For loop over each row (terminate the For when you get a hit). Something like

Sub Demo()
    Dim rng As Range, cl As Range
    Dim someRange As Range
    Dim AreaRange As Range
    Dim DeletedRow As Boolean
    Dim areaNum As Long
    Dim i As Long

    ' Set your test range, eg
    Set someRange = [A1:F3,A8:F10,A16:F19]
    areaNum = 1
    ' Set first row to test
    Do
        ' Track discontiguous range areas
        Set AreaRange = someRange.Areas(areaNum)
        Set rng = someRange.Areas(areaNum).Rows(1)
        Do Until rng Is Nothing
            DeletedRow = False
            For Each cl In rng.Cells
                ' test condition, eg empty
                If IsEmpty(cl) Then
                    ' update rng to next row.  do this before the Delete
                    Set rng = Application.Intersect(someRange, rng.Offset(1, 0))
                    ' delete row
                    cl.EntireRow.Delete
                    DeletedRow = True
                    ' stop looping the row
                    Exit For
                End If
            Next
            ' if not already updated...
            If Not DeletedRow Then
                Set rng = Application.Intersect(someRange, rng.Offset(1, 0))
            End If
        Loop

        ' not all rows in area have been deleted, increment AreaNum
        ' AreaRange end up in a state where it's not Nothing, and isn't valid
        On Error Resume Next
            i = 0
            i = AreaRange.Count
        On Error GoTo 0
        If i > 0 Then
            areaNum = areaNum + 1
        End If
    Loop Until areaNum > someRange.Areas.Count
End Sub

Note that this will be quite inefficient, but does meet your stated aims