Deleting Contents of Empy Cells with Macro

[TooZippy]

Hi,

I am using this Macro and it does not do exactly what I want. It deletes the contents of the cells that I highlight but not the cells that are not highlighted in a column even though all of those cells have a null value in them. I want it to go through all of the rows in a column and delete the contents of the cells that have a null value in them that appear empty. Can anybody help please?

Thank you,

Jared Z.

Sub clear_empty_cell()
   
   Application.ScreenUpdating = False
   Dim cell As Range
      
     ActiveSheet.UsedRange.Replace What:=Chr(160), Replacement:=Chr(32), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   On Error Resume Next   'in case no text cells in selection
   For Each cell In Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Try this

Sub clear_empty_cell()
   
    Application.ScreenUpdating = False
    Dim cell As Range
    
    ActiveSheet.Cells.Replace What:=Chr(160), Replacement:=Chr(32), _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    On Error Resume Next   'in case no text cells in selection
    For Each cell In ActiveSheet.Cells.SpecialCells(xlConstants, xlTextValues)
        cell.Value = Application.Trim(cell.Value)
    Next cell
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[TooZippy]

Thank you. I will give a try later on today and post a reply to let you know if it works or not.

JZ

 

[TooZippy]

Dante,

I tried your Macro this morning and it works great! Thank you for responding to my thread.

Jared Z.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.