Delete rows that contain formulas but are blank

[jamesw1123]

I am pulling information from other pages and I need to delete the rows that there is no information in them, but the formulas are in them. I also need to specify a range of cells. The range I am using is A26:A55. This formula will not work for me, because it does not read the cells with formulas, even though they display a blank, as a blank.

Range(A26:A55).SpecialCells(xlCellTypeBlanks).EntireRow.delete

I need a little help from the Excel Gods (which are you guys)

Thanks

 

[Gary McMaster]

Maybe this?

Gary

Dim oCell As Range
Dim oTarget As Range

Set oTarget = ActiveSheet.Range("A26:A55")

For Each oCell In oTarget
    If oCell.HasFormula And oCell.Text = "" Then
        MsgBox oCell.Address & " has a formula but no text"
        'oCell.EntireRow.Delete
    End If
Next oCell

End Sub

 

[Case_Germany]

Hi,

"SpecialCells" provides in a series of cases an incorrect result. If the whole row is empty (except for the formula), then try the following:

Option Explicit
Sub Main()
    Dim rngRange As Range
    Dim rngCell As Range
    On Error GoTo Fin
    'For Each rngCell In Selection ' a marked Range
    For Each rngCell In Range("A26:A55") ' if necessary, adjust the Range
        If WorksheetFunction.CountBlank(Rows(rngCell.Row)) = Columns.Count Then
            If rngRange Is Nothing Then
                Set rngRange = Rows(rngCell.Row)
            Else
                Set rngRange = Union(rngRange, Rows(rngCell.Row))
            End If
        End If
    Next rngCell
    If Not rngRange Is Nothing Then rngRange.Delete
Fin:
    If Err.Number <> 0 Then MsgBox "Fehler: " & _
        Err.Number & " " & Err.Description
End Sub

Test it on a backup copy of the file.

 

[jamesw1123]

Hi Gary. This works good, but once I have it start deleting cells the cell numbers change so it's not testing the row that moved up , which is now the last number it tested. Wow! I think I confused myself with that statement.

For example, 26=false - not deleted, 27=true - delete entire row, the row that was 28 which is true now becomes 27, but it is skipped because 27 has already been tested. I hope that makes a little more sense.

Any thoughts

Maybe this?

Gary

Dim oCell As Range
Dim oTarget As Range

Set oTarget = ActiveSheet.Range("A26:A55")

For Each oCell In oTarget
    If oCell.HasFormula And oCell.Text = "" Then
        MsgBox oCell.Address & " has a formula but no text"
        'oCell.EntireRow.Delete
    End If
Next oCell

End Sub

 

[Gary McMaster]

Sorry, I should have known better than that.

Try this:

Public Sub Test()

Dim oCell As Range
Dim oTarget As Range
Dim oDelete As Range

Set oTarget = ActiveSheet.Range("A26:A55")

For Each oCell In oTarget
    If oCell.HasFormula And oCell.Text = "" Then
        If Not oDelete Is Nothing Then
            Set oDelete = Union(oDelete, oCell.EntireRow)
        Else
            Set oDelete = oCell.EntireRow
        End If
    End If
Next oCell

oDelete.Interior.ColorIndex = 3
'oDelete.Delete

End Sub

Another, and maybe even more popular, method is to use a "For Next" loop and step backwards thru the range which avoids this problem.

Gary

 

[jamesw1123]

Your the man. This worked perfectly. Thanks

 

[Gary McMaster]

You're welcome. Thanks for the feedback.

Here's a sample of the backwards "For Next". Probably the more common way to do it.

Gary

Public Sub Test()

Dim oCell As Range
Dim lRowCount As Long

For lRowCount = 55 To 26 Step -1
    Set oCell = ActiveSheet.Range("A" & lRowCount)
    If oCell.HasFormula And oCell.Text = "" Then
        oCell.EntireRow.Interior.ColorIndex = 3
        'oCell.EntireRow.Delete
    End If
Next lRowCount

End Sub