We help IT Professionals succeed at work.

Deleting rows that are missing data

carlosab
carlosab asked
on
Dataset has a variable size.
If there is information in a row, then, there is always data in column B in that row.
But, sometimes there data is missing from Column H or J. If so, I want to delete that row.

This is the code I'm starting with, but, I've not been able to adjust it enough to test just one column (let alone 2).

Thanks.
Private Sub DeleteRowsWithNoDefendantsLastName()
'   Delete rows that have a value of Delete in Column B.
    Dim rng1 As Range
    Dim rng2 As Range
    Dim lngRow As Long
    Application.ScreenUpdating = False
    If [b2] <> vbNullString Then
        Set rng1 = Range([b2], [b1].End(xlDown))
    Else
        Set rng1 = [b1]
    End If
    For lngRow = rng1.Rows.Count To 2 Step -1
        If (Cells(lngRow, "H") = vbNullString) Then
            'Rows(lngRow).EntireRow.Delete
            If rng2 Is Nothing Then
                Set rng2 = Rows(lngRow)
            Else
                Set rng2 = Union(rng2, Rows(lngRow))
            End If
        Else
        '    If Left$(Cells(lngRow, "c"), 2) <> "CV" Then Rows(lngRow).EntireRow.Delete
        End If
    Next
            rng2.EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

Open in new window

Comment
Watch Question

Try this:
Private Sub DeleteRowsWithNoDefendantsLastName()
'   Delete rows that have a value of Delete in Column B.
    Dim rng1 As Range
    Dim rng2 As Range
    Dim lngRow As Long
    Application.ScreenUpdating = False
    
    Set rng1 = Range("B1", Range("B" & Rows.Count).End(xlUp))
    For lngRow = rng1.Rows.Count To 2 Step -1
        If IsEmpty(Cells(lngRow, "H")) Or IsEmpty(Cells(lngRow, "J")) Then
            'Rows(lngRow).EntireRow.Delete
            If rng2 Is Nothing Then
                Set rng2 = Rows(lngRow)
            Else
                Set rng2 = Union(rng2, Rows(lngRow))
            End If
        Else
        '    If Left$(Cells(lngRow, "c"), 2) <> "CV" Then Rows(lngRow).EntireRow.Delete
        End If
    Next
    rng2.EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
Great. Thanks.