Delete rows IF all cells in selected ROW is BLANK-CodePudding

I have financial data where some rows are blank and id like to be able to delete the entire row IF entire rows in a selected range are blank (its important for it to be in selected range as I might have "Revenues" in column A but then I have column B-D be blank data (no numbers basically)).

I'd like for it to apply to a selected range, instead of having a predetermined range in the code (for the code to be flexible).

I am trying to use this format but it doesnt seem to be working:

Sub deleteBlankRows()
Dim c As Range

On Error Resume Next
For Each c In Selection.Row.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next
End Sub

Any thoughts would be welcome.

CodePudding user response：

Loop trough each complete row of selection and check if the count of blank cells matchs the count of all cells in row:

My code:

Dim rng As Range

For Each rng In Selection.Rows
    If Application.WorksheetFunction.CountBlank(rng) = rng.Cells.Count Then rng.EntireRow.Delete
Next rng

After executing code:

The emtpy row is gone

UPDATE:

@VasilyIvoyzha is absolutely right. For each won't work properly on this situation. A better approach would be:

Dim i&, x&, lastRow&
lastRow = Range(Split(Selection.Address, ":")(1)).Row
x = Selection.Rows.Count
For i = lastRow To Selection.Cells(1).Row Step -1
    If WorksheetFunction.Concat(Selection.Rows(x)) = "" Then Rows(i).Delete
    x = x - 1
Next i

This way will delete empty rows on selection, even if they are consecutive.