Eliminate blank spaces in VBA

Question

I have a standard worksheet exported from the system and when there is no information for the cell, the system fills it with blank spaces. I use a variable to receive the cell value and then check if there's any information. When there is, I eliminate blank spaces on both sides of the string using Trim, but, when there is no information in the cell, the variable receive a chain of blank spaces, which Trim doesn't work. Example: Cell with information - " 1A90 ". Using Trim - "1A90" Cell without information - " ". Using Trim - " ". I want to convert the result to empty or "". Anybody can help me?

Answer 1

Select the range you want to clean and run this sub routine.

Sub CleanTrimCells_Evaluate()
SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim rng As Range
Dim Area As Range

'Weed out any formulas from selection
  If Selection.Cells.Count = 1 Then
    Set rng = Selection
  Else
    Set rng = Selection.SpecialCells(xlCellTypeConstants)
  End If

'Trim and Clean cell values
  For Each Area In rng.Areas
    Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
  Next Area
  
End Sub