Removing spaces from cells that have no visible content

[xcellrodeo]

Hi there, I have a set of data (example below) which runs from Col.B->AM. Number of rows varies.
What I would like to achieve is to have a VBA script that searches
through the columns starting from Col.E and ending in Col.AM to remove any spaces.
However this should only be done in cells that have no visible content so as to preserve any cells with content where the spaces are legitimate. The number of rows will depend on the size of the data which is variable.But for this, a count function could be added. Please note that empty columns between Col.E & AM can vary as below example only shows Col I & J as having no visible content.
Any help would be appreciated.Thanks

A

B

C

D

E

F

G

H

I

J

K

L

2

P/N

COUNT

COMPLETE

SUPPL

CO1

CO2

CO3

CO4

CO5

CO6

CO7

CO8

3

K550

6

ONE LINE

90893R 200

TEC

R-48245

COR

OP-09 0223

TRI

9823 454

<tbody>
</tbody>

 

[Andrew Poulsom]

If the cells have only a single space you can use Replace, matching the entire contents (LookAt:=xlWhole).

 

[xcellrodeo]

Hi Andrew, thanks for your suggestion.Unfortunately however there are no guarantees that cells with no visible value have only a single space. Some may have multiple.

 

[Peter_SSs]

This may be a bit "brute force" but if you were able to put some limit on how many spaces there could be (I've used 10) then maybe something like this might be of use to you.

Sub Clear_Space_Cells()
  Dim i As Long
  
  Const MaxSpaces As Long = 10
  
  Application.ScreenUpdating = False
  For i = 1 To MaxSpaces
    Columns("B:AM").Replace What:=String(i, " "), Replacement:="", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=False
  Next i
  Application.ScreenUpdating = True
End Sub

 

[imaseo]

Hey I'm pretty new at using VBA and this is probably a really inefficient way of doing it but I made a simple macro.

Sub RemoveSpaces()
Dim LastColumn As Integer
Dim LastRow As Integer
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


For h = 1 To LastRow


    For i = 1 To LastColumn
    If Cells(h, i).Value = " " Then
      Cells(h, i).Value = Empty
    ElseIf Cells(h, i) = "  " Then
      Cells(h, i).Value = Empty
    ElseIf Cells(h, i) = "   " Then
      Cells(h, i).Value = Empty
    ElseIf Cells(h, i) = "    " Then
      Cells(h, i).Value = Empty
    ElseIf Cells(h, i) = "     " Then
      Cells(h, i).Value = Empty
    ElseIf Cells(h, i) = "      " Then
      Cells(h, i).Value = Empty
    ElseIf Cells(h, i) = "       " Then
      Cells(h, i).Value = Empty
    ElseIf Cells(h, i) = "        " Then
      Cells(h, i).Value = Empty
    End If
    Next
Next
End Sub

 

[xcellrodeo]

Thank you Gents for your input. Peter, I ended up using your script which works well.

 

[Peter_SSs]

Thank you Gents for your input. Peter, I ended up using your script which works well.

Glad it worked for you, thanks for letting us know.