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

Darren_workforce

Board Regular
Joined
Oct 13, 2022
Messages
102
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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
 
Or maybe i missed something. Anyway id do something like:

VBA Code:
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.
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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:
VBA Code:
    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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top