Delete Rows in Table if Field is Blank

[gheyman]

I am getting an error message: Run Time 1004 Delete Method of Range Class Failed

Here:
Sheets("DS_List").Range("E16:E" & LRowDS).Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Any help is appreciated.

Sub LoadDownSelect()

Application.ScreenUpdating = False

'*******************************************************************
'Unfilter the CMCS Tracker
Sheets("CMCS Tracker").Select
ActiveSheet.AutoFilterMode = False
    Rows("36:36").Select
    Selection.AutoFilter

'*******************************************************************

'First Clear Table

'Clear table
                   
'Clear table - remove all rows except the first row
    Application.ScreenUpdating = False
    
    Sheets("DS_List").Select

    ActiveSheet.ListObjects("DownSelectTable").HeaderRowRange.Select
    'Remove the filters if one exists.
    If ActiveSheet.FilterMode Then
        Selection.AutoFilter
    End If

    'Clear all lines but the first one in the table leaving formulas for the next go round.
    With Worksheets("DS_List").ListObjects("DownSelectTable")
        .Range.AutoFilter
        On Error Resume Next
        .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete

        If .ListColumns.Count > 1 Then
            .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
        Else
            
            With .DataBodyRange.Cells(1)
            If Not .HasFormula Then .ClearContents

            End With
        
        End If

    End With
    
    Range("B16").Select
    


    
'*******************************************************************
'Copy to SownSelect
'*********************************************************************
Sheets("DS_List").Activate
Sheets("DS_List").Range("B16").Select
'

Dim LastRow As Long

LastRow = Sheets("CMCS Tracker").Cells(Rows.Count, "B").End(xlUp).Row


Sheets("CMCS Tracker").Range("P37:P" & LastRow).Copy
    Sheets("DS_List").Range("B16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False

Sheets("CMCS Tracker").Range("J37:J" & LastRow).Copy
    Sheets("DS_List").Range("C16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    
Sheets("CMCS Tracker").Range("C37:C" & LastRow).Copy
    Sheets("DS_List").Range("D16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        
Sheets("CMCS Tracker").Range("I37:I" & LastRow).Copy
    Sheets("DS_List").Range("E16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False

'**********************************************************
'Remove Blanks and Dups

'Remove Duplicates
      Range("DownSelectTable[#All]").Select
    ActiveSheet.Range("DownSelectTable[#All]").RemoveDuplicates Columns:=Array(1, _
        2, 3), header:=xlYes
        
'Delete Rows in  table where CostSourceId is blank
Dim rngBlanks As Excel.Range

With Worksheets("DS_List").ListObjects("DownSelectTable")
    On Error Resume Next
    Set rngBlanks = Intersect(.DataBodyRange, .ListColumns("CostSourceId").Range).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rngBlanks Is Nothing Then
        rngBlanks.Delete
    End If
End With


'Delete Rows in  table where DSRationale is blank

Sheets("DS_List").Select

Dim LRowDS

LRowDS = Sheets("DS_List").Cells(Rows.Count, "B").End(xlUp).Row

Sheets("DS_List").Range("E16:E" & LRowDS).Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete


Sheets("DS_List").Range("B16").Select


End Sub

Thank you

 

[gheyman]

Ugh, I could just use the same code that deletes blanks in CostSourceID

Dim rngBlanks As Excel.Range

With Worksheets("DS_List").ListObjects("DownSelectTable")
On Error Resume Next
Set rngBlanks = Intersect(.DataBodyRange, .ListColumns("CostSourceId").Range).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rngBlanks Is Nothing Then
rngBlanks.Delete
End If
End With

Got it. Thank you for your time