Create an union range out of the results of a loop-CodePudding

Basically, I want to create a loop that loops through a big range and if that cell isn't a certain value it deletes the whole column outright. I'm guessing the best way is create an union of all the ranges to delete and after the loop is done delete the whole range. I tried to do it deleting the column during the loop itself. Stepping through the subroutine was fine but if I just executed it the whole thing would freeze up. Here's a small example (I lost my code when it froze up).

Dim NoOfRows as long
Dim Something as range

For i = 2 to NoOfRows

    if not Something.offset(i,0).value = "A" and not Something.offset(i,0).value = "B" then
        Something.offset(i,0).EntireRow.Delete
        i = i - 1
    End If
Next

I'm guessing this proved somewhat heavy but I wanted to make a code that was resource intensive. Thanks

I tried running it the described way and it crashed

CodePudding user response：

You can either loop in reverse and delete, to avoid issues with changing your iterator, or you can create a union() of the range, similar to:

'reverse loop
For i = lastRow to 1 Step -1
    If .Cells(i,1).Value = vbNull Then .Rows(i).Delete
Next i

'union with standard loop
For i = 1 to lastRow
    If .Cells(i,1).Value = vbNull Then
        If deleteRange Is Nothing Then
            Set deleteRange = .Rows(i)
        Else
            Set deleteRange = Union(deleteRange, .Rows(i))
        End If
    End If
Next i

CodePudding user response：

I think the problem is what your code does after it deletes the row. You are using "i" as a counter so by doing this: i = i - 1 after each row supression it's keeping you from reaching "NoOfRows" condition, so it turns into an infinite loop.

i = 2
Do until i > NoOfRows
    If Not Something.Offset(i, 0).Value = "A" And Not Something.Offset(i, 0).Value = "B" Then
        Something.Offset(i,0).EntireRow.Delete
        NoOfRows = NoOfRows - 1
    Else:
     i = i   1
    End If
Loop

For Loops in VBA don't like when you change the stopping condition during the iteration process, that's why in these cases I prefer to use While or Until to save headaches. I hope this gives you an idea of what you need to change in order to fix your code.