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]

Sorry. The full range of data goes from A:O. There will always be data in Columns A and B which is why I just want to search C:O for blank cells.

 

[Joe4]

I think you could replace this:

    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

with this:

    For i = n To 1 Step -1
        Set rng = Range(Cells(i, "C"), Cells(i, "O"))
        If Application.WorksheetFunction.CountBlank(rng) = 13 Then Rows(i).Delete
    Next i

 

[Darren_workforce]

I think you could replace this:

    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

with this:

    For i = n To 1 Step -1
        Set rng = Range(Cells(i, "C"), Cells(i, "O"))
        If Application.WorksheetFunction.CountBlank(rng) = 13 Then Rows(i).Delete
    Next i

Thank you Joe4! That was exactly what was needed. Much appreciated!!

 

[Joe4]

You are welcome.
Glad I was able to help!

Hopefully, it is pretty straightforward and you can see what is going on, but let me know if you have any questions about it.

 

[Darren_workforce]

You are welcome.
Glad I was able to help!

Hopefully, it is pretty straightforward and you can see what is going on, but let me know if you have any questions about it.

I need to take a course on advanced vba, honestly. Assigning things like ranges and such gets complicated for me. I find most of what I'm looking for online and try to tweak it but I doubt I'd ever have figured out how to adjust the range on this myself. I'd love to be able to review a code and determine what needs to take place as quickly as the pros like you.

 

[Joe4]

I need to take a course on advanced vba, honestly. Assigning things like ranges and such gets complicated for me. I find most of what I'm looking for online and try to tweak it but I doubt I'd ever have figured out how to adjust the range on this myself. I'd love to be able to review a code and determine what needs to take place as quickly as the pros like you.

I am a big fan with using Cells(row, column) instead of Range.
They really mean the same thing, but the big advantage with Cells is that for the column reference, you can use the column reference ("A", "B", "C", etc) or the column index (1,2,3).
Using the index makes it MUCH easier to loop through columns.

So

Range("C8")

is the same as:

Cells(8, "C")

is the same as:

Cells(8, 3)

I found this write-up on dealing with Range/Cells that you might find useful: Excel VBA Range – Working with Range and Cells in VBA

 

[steve the fish]

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

so that if a row has blank cells all the way across range C:O

 

[Darren_workforce]

I am a big fan with using Cells(row, column) instead of Range.
They really mean the same thing, but the big advantage with Cells is that for the column reference, you can use the column reference ("A", "B", "C", etc) or the column index (1,2,3).
Using the index makes it MUCH easier to loop through columns.

So

Range("C8")

is the same as:

Cells(8, "C")

is the same as:

Cells(8, 3)

I found this write-up on dealing with Range/Cells that you might find useful: Excel VBA Range – Working with Range and Cells in VBA

OK thank you. I'll check it out once I'm off for the day.

 

[steve the fish]

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