VBA code to trim spaces and tab from blank cells

[singhabhijitkumar]

I have cells with no data but simply blank spaces or tabs. I want to delete the spaces/tabs from such cells

[karan]

Select all the cells & run this code:

Sub trimall()

Dim cell As Range

For Each cell In Selection
    cell = Application.WorksheetFunction.Trim(cell)
    cell = Application.WorksheetFunction.Clean(cell)
Next

MsgBox ("Done")

End Sub

This would remove all terminal & extra spaces & non-printable characters.

Alternatively, you can also select all cells, do a Ctrl + H & replace space with nothing.

Regards,
Karan

[singhabhijitkumar]

hi, instead of selection can you please modify it so that it trims blanks spaces and tabs from sheet irrespective of selection

[rwgrietveld]

Dim Ccell As Range

For Each Ccell In Range(Cells(1, 1), Cells(1, 1).SpecialCells(xlCellTypeLastCell))
  Ccell = Trim(Ccell)
Next

or

Dim Ccell As Range

For Each Ccell In Cells(1, 1).SpecialCells(xlCellTypeConstants, 23)
  Ccell = Trim(Ccell)
Next

[DonkeyOte]

Ricardo, FWIW using either of:

Application.Transpose / Application.WorksheetFunction.Transpose

is generally preferable than using VBA Trim because the VBA equiv. does not remove superfluous spaces mid string unlike the native version.

To illustrate (via Immediate Window in VBE):

?Trim("Apples   Oranges")

?Application.Trim("Apples   Oranges")

In this instance the cells contain only blank spaces then obviously the VBA Trim would suffice but the above is just worth bearing in mind in general terms...