How to delete entire row based on multiple call range being blank

[Darren_workforce]

Hello,

Using the code below, I'm close to what I need but it's not perfect. I have a range of data (generally not going to be more than 100 rows at most). I run a macro to change any cells with '0' data to a blank cell. Afterwards, there could still be 1-2 cells in a row that still contain data. However, using the formula below, it still removes those rows and that's what I'm trying to avoid.

How would I adjust the formula (or is it the wrong formula to use) so that if a row has blank cells all the way across range C:O, that entire row will be removed and then have the remaining rows underneath moved up. But then if even 1 cell within the C:O range contains data, that row will be left alone and remain after the macro runs.

Thank you in advance!

Sub DelBlankRows()

Worksheets("Raw Data").Activate

Dim i As Long, n As Long
Dim rg As Range
Application.ScreenUpdating = False
With ActiveSheet
    Set rg = Intersect(.UsedRange, .Columns("C:O"))
    n = rg.Rows.Count
    For i = n To 1 Step -1
        If Not IsError(rg.Cells(i, 1).Value) Then
            If rg.Cells(i, 1).Value = 0 Or rg.Cells(i, 1).Value = "" Then rg.Rows(i).EntireRow.Delete
        End If
    Next
End With

   End Sub

 

[Darren_workforce]

Or maybe i missed something. Anyway id do something like:

Sub DelBlankRows()

Dim i As Long, counter As Long
Dim rg As Range, arr

Application.ScreenUpdating = False
With Worksheets("Raw Data")
    Set rg = .Range("C:O")
    lr = rg.Find("*", , , , xlByRows, xlPrevious).Row
    arr = .Range("C1:O" & lr)
    For i = UBound(arr, 1) To 1 Step -1
        counter = 0
        For j = 1 To UBound(arr, 2)
            If Not IsError(arr(i, j)) Then
                If arr(i, j) = 0 Or arr(i, j) = "" Then
                    counter = counter + 1
                End If
            End If
        Next
        If counter = rg.Columns.Count Then .Rows(i).Delete Shift:=xlUp
    Next
End With
Application.ScreenUpdating = True

End Sub

That also appears to do what I need. Thank you as well, Steve! It's nice that Excel allows multiple routes to reach the same destination.

 

[Joe4]

Not sure that does what you asked for. Its still only testing the first cell in the row

Are you referring to my code amendment?
How do you figure?

I am just replacing the For loop in his code.
This part gets the rows that the loop uses:

    Set rg = Intersect(.UsedRange, .Columns("C:O"))
    n = rg.Rows.Count

I would also recommend NOT looping throigh each column individually. There is no need to do that, and adding more loops slows the code down (loops are notoriously slow, so should be limited in use, when possible).

 

[steve the fish]

My code doesnt loop through columns. It loops through an array so would be speedy. It could be made quicker by deleting all rows in one hit but apparently only 100 rows so no real need.

 

[Joe4]

My code doesnt loop through columns. It loops through an array so would be speedy. It could be made quicker by deleting all rows in one hit but apparently only 100 rows so no real need.

OK, I see. It is looping through arrays that represent both rows and columns.
So it is doing a double-loop (two FOR loops), but through array values.
I admit I don't use arrays very much.