Deleting spaces from blank cell in sheet?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,356
Hi Everyone.
I need a macro that can help me clean up my data range.
We export data from our CRM system which is very old and the data needs sorting out before i can do anything with it.
I've done most of the macros but am stuck and looking for help.

I need a macro to help me empty out cells that are supposed to be blank.
Some of the blank cells have spaces or invisible data in them (what i mean is there no databut they don't register as "" when i do my formulas)
So what i'm hoping for is some way to have a macro make all cells that have a space but nothing else in them empty.
please help if you can.

Thanks
Tony
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,076
Office Version
2019
Platform
Windows
Code:
Option Explicit


Sub Clean()
Dim c As Range, rng As Range
Set rng = Range("A1:B10") 'Set your own range
For Each c In rng
c = Trim(c)
Next c
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
If you put this in an unused cell & point it to one of the "blank" cells, what does it say?
=CODE(a1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
In that case try
Rich (BB code):
Sub tonywatson()
   ActiveSheet.UsedRange.Replace Chr(160), "", xlWhole, , , , False, False
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top