Clearing duplicates from an Excel 2007 sheet

This is the original code I had for removing duplicates from a sheet:

Sub Remove_Duplicates()
'
Application.ScreenUpdating = False

    ActiveSheet.Range("$A$1:$Z$2000").RemoveDuplicates Columns:=Array(5, 10, 11, 12, 13, 14, 15, 16), Header:=xlYes

Range("C" & Rows.Count).End(xlUp).Offset(1).Select

Application.ScreenUpdating = True

Call Hide_Columns_Swivel

End Sub

I added a section to this code (see below) that clears contents of fake empty cells. But now the code takes up to 15 seconds to run. It works as it is suppose to, just really slowly.

Sub Remove_Duplicates()
'
Application.ScreenUpdating = False

Dim usedrng As Range

    ActiveSheet.Range("$A$1:$Z$2000").RemoveDuplicates Columns:=Array(5, 10, 11, 12, 13, 14, 15, 16), Header:=xlYes

    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.Value = "" Then
            usedrng.ClearContents
        End If
    Next

Range("C" & Rows.Count).End(xlUp).Offset(1).Select

Application.ScreenUpdating = True

Call Hide_Columns_Swivel

End Sub

I need help making this more efficient as I do not know enough about VBA to do that. All assistance is greatly appreciated.

If you are trying to get zero-length strings left after reverting formulas (that may have resulted in "" ) to their values, the quickest way is to spin through the columns and apply a quick Text-to-Columns, Fixed Width, Finish to each.

Dim c As Long
With worksheets("Sheet1")
    With .Range("A1").CurrentRegion  `<~~ set to the desired range of one or more columns
        For c = 1 To .Columns.Count
            .Columns(c).TextToColumns Destination:=.Columns(c), _
                DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
        Next c
    End With
End With

This process will take zero-length strings and make then truly blank cells. This is an important step in preppinga worksheet for direct non-CSV import to MS-SQL.

StackExchange Code Review Q#116261, answer score: 12