Delete Rows in a table if blank

[gheyman]

I have this code that seems to work for everything except deleting rows if DSRationale is blank

I checked the table field name and it is "DSRationale"

I know the tab name and table name is correct because the code does delete rows if "CostSourceId" is blank.

Does anyone see where I made the mistake? Its not deleting rows where the DSRationale is blank. Thanks!!

Sub TestThis()

'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("DownSelctions").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
Dim rngBlankDS As Excel.Range

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

End Sub

 

[gheyman]

I figured out the problem, but not a solution.

The rows in the column appear to be blank but I am guessing they are not actually blank. If I click into one of them and hit delete, and then run the code - that row does get deleted.

So now my question is how do I delete these rows if null? I guess!!

 

[alansidman]

How to display or view non-printing characters in Excel?

Is there an option in MS Excel 2010 that will display non-printing characters within a cell (e.g. spaces or the linebreak character introduced by pressing Alt-Enter)?

superuser.com

this link will help to identify that you have non printing characters. You may want to include a step in your macro to clean your range of data and see if that solves your issue.

 

[gheyman]

Thanks, I will try that.

 

[gheyman]

I am now clearing that row of data in the very begining steps.

This seems to be working

Sub ClearcellsX ()
Dim CMCSLR As Long

    CMCSLR = Sheets("CMCS Tracker").Cells(Rows.Count, "C").End(xlUp).Row

    Sheets("CMCS Tracker").Range("I37:I" & CMCSLR).Select
        Selection.ClearContents
End Sub

 

[Georgiboy]

I am now clearing that row of data in the very begining steps.

This seems to be working

Sub ClearcellsX ()
Dim CMCSLR As Long

    CMCSLR = Sheets("CMCS Tracker").Cells(Rows.Count, "C").End(xlUp).Row

    Sheets("CMCS Tracker").Range("I37:I" & CMCSLR).Select
        Selection.ClearContents
End Sub

Drop the select part:

Sub ClearcellsX()
Dim CMCSLR As Long

    CMCSLR = Sheets("CMCS Tracker").Cells(Rows.Count, "C").End(xlUp).Row

    Sheets("CMCS Tracker").Range("I37:I" & CMCSLR).ClearContents
End Sub

 

[gheyman]

Thanks!