CODEWITHSUNDEEP
excelvba
icalderon
icalderon

Reputation: 63

How to delete a row if there is no value in a column?

I'm trying to delete rows in table if there is no value in a certain column.

I've used a code that deletes rows if there is one cell value missing, but I would like to delete rows if a cell does not contain a value in a certain column.

For example, if there is no value in Column G Row 5 then I want to delete the entire row.

Sub Test2()

  Dim rng As Range
  On Error Resume Next
  Set rng = Range("Table3").SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0
  If Not rng Is Nothing Then
    rng.Delete Shift:=xlUp
  End If

End Sub

This deletes all rows with any type of missing cell value.

Upvotes: 0

Views: 228

Answers (1)

BigBen
BigBen

Reputation: 49998

Two small changes:

Sub Test2()

    Dim rng As Range
    On Error Resume Next
    Set rng = Range("G:G").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If Not rng Is Nothing Then
        rng.EntireRow.Delete Shift:=xlShiftUp
    End If

End Sub

EDIT:

If you want to work directly with the table, then consider iterating over the ListRows of the table in question, something like this:

Sub Test2()
    Dim myTbl As ListObject
    Set myTbl = Sheet1.ListObjects("table3") ' change sheet as necessary

    Dim indx As Long
    indx = myTbl.ListColumns("ColumnName").Index

    Dim rngToDelete As Range

    Dim myRw As ListRow
    For Each myRw In myTbl.ListRows
        If IsEmpty(myRw.Range(1, indx).Value) Then
            If rngToDelete Is Nothing Then
                Set rngToDelete = myRw.Range
            Else
                Set rngToDelete = Union(rngToDelete, myRw.Range)
            End If
        End If
    Next myRw

    If Not rngToDelete Is Nothing Then
        rngToDelete.Delete Shift:=xlShiftUp
    End If
End Sub

Note: Technically, it's xlShiftUp, not xlUp.

Upvotes: 1

Related Questions