Removing spaces from cells that have no visible content

xcellrodeo

Board Regular
Joined
Oct 27, 2008
Messages
209
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
[TABLE="width: 668"]
<tbody>[TR]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: #E5E0EC"]A[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]B[/TD]
[TD="width: 87, bgcolor: #E5E0EC"]C[/TD]
[TD="width: 83, bgcolor: #E5E0EC"]D[/TD]
[TD="width: 85, bgcolor: #E5E0EC"]E[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]F[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]G[/TD]
[TD="width: 86, bgcolor: #E5E0EC"]H[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]I[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]J[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]K[/TD]
[TD="width: 64, bgcolor: #E5E0EC"]L[/TD]
[/TR]
[TR]
[TD="bgcolor: #E5E0EC"]2[/TD]
[TD="bgcolor: transparent"]P/N[/TD]
[TD="bgcolor: transparent"]COUNT[/TD]
[TD="bgcolor: transparent"]COMPLETE[/TD]
[TD="bgcolor: transparent"]SUPPL[/TD]
[TD="bgcolor: transparent"]CO1[/TD]
[TD="bgcolor: transparent"]CO2[/TD]
[TD="bgcolor: transparent"]CO3[/TD]
[TD="bgcolor: transparent"]CO4[/TD]
[TD="bgcolor: transparent"]CO5[/TD]
[TD="bgcolor: transparent"]CO6[/TD]
[TD="bgcolor: transparent"]CO7[/TD]
[TD="bgcolor: transparent"]CO8[/TD]
[/TR]
[TR]
[TD="bgcolor: #E5E0EC"]3[/TD]
[TD="bgcolor: transparent"]K550[/TD]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent"]ONE LINE[/TD]
[TD="bgcolor: transparent"]90893R 200[/TD]
[TD="bgcolor: transparent"]TEC[/TD]
[TD="bgcolor: transparent"]R-48245[/TD]
[TD="bgcolor: transparent"]COR[/TD]
[TD="bgcolor: transparent"]OP-09 0223[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TRI[/TD]
[TD="bgcolor: transparent"]9823 454[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
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.
 
Upvote 0
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.

Rich (BB code):
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
 
Upvote 0
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.

Code:
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top