VBA code to trim spaces and tab from blank cells

**singhabhijitkumar** · 02-11-2010, 03:20 AM

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

**karan** · 02-11-2010, 03:50 AM

Select all the cells & run this code:

Please Login or Register  to view this content.

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** · 02-11-2010, 06:19 AM

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

**rwgrietveld** · 02-11-2010, 06:44 AM

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**DonkeyOte** · 02-11-2010, 06:48 AM

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):

Please Login or Register  to view this content.

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...