VBA - Clear "cells that look blank but AREN'T actually blank"

[ckp90c]

Hi All - I have a macro coded below that looks in Column A and will essentially clear cells that look blank but aren't actually blanks. It does work, however it is very slow and I don't think I can make it client-facing in its current state. Is there a way to make it run faster and/or prettier? Thank you!




Sub ClearEmptyValues()


Application.ScreenUpdating = False


For Each cell In UsedRange.Columns("A").Cells
If Not IsEmpty(cell) And cell.Value = vbNullString Then cell.Formula = vbNullString
Next


End Sub

 

[Fluff]

If col A is the result of pasting formula using paste as value then try

Sub MakeEmpty()
With Intersect(ActiveSheet.UsedRange, Columns(1))
   .Value = .Value
End With
End Sub

 

[ckp90c]

Hey this unfortunately isn't working, I am getting a 400 error. Any advice?

 

[ckp90c]

Hey actually I think I have it now...I put the code in a Module, vs the sheet itself and it seems fine now. Thank you!

 

[Fluff]

Glad it's sorted & thanks for the feedback