Remove extra spaces from cells in column

Question

I wrote the following code to do inventory scanning bar-codes but for some reason when I scan the bar-code it is adding extra spaces in the cells and the result are not showing up as expected.

How do I remove the extra spaces from the cells in column?

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Cells.Count > 1 Or IsEmpty(Target) Or Target.Column <> 1 Then Exit Sub

    If Not SheetExists("WarehouseInventory") Then Exit Sub

    Dim result As Variant

    Set result = Sheets("WarehouseInventory").Cells.Range("E:E").Find(Target)

    If result Is Nothing Then
       Target.Worksheet.Cells(Target.Row, 2) = "Data Maybe Bin #?"
    Else
        Target.Worksheet.Cells(Target.Row, 2) = result.Worksheet.Cells(result.Row, 4)
        Target.Worksheet.Cells(Target.Row, 3) = result.Worksheet.Cells(result.Row, 5)
        Target.Worksheet.Cells(Target.Row, 4) = result.Worksheet.Cells(result.Row, 6)
        Target.Worksheet.Cells(Target.Row, 5) = result.Worksheet.Cells(result.Row, 7)
    End If

End Sub

Public Function SheetExists(SheetName As String) As Boolean
    Dim ws As Worksheet
    SheetExists = False

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = SheetName Then SheetExists = True
    Next ws

End Function

Barcode will be scan on column A

enter image description here

Same question has already been solved here http://stackoverflow.com/questions/9578397/remove-leading-or-trailing-spaces-in-an-entire-column-of-data — NickOS, May 14 '15 at 06:07

score 2 · Answer 1 · edited Jul 05 '15 at 18:44

2

This is a code for trimming cells of extra space.

Dim cell As Range
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
cell = WorksheetFunction.Trim(cell)
Next cell

The above code will Trim all the cells in the ActiveSheet. Select appropriate cells which you want to trim, and apply the Trim(cell) on them.

edited Jul 05 '15 at 18:44

0m3r

12,286
15
35
71

answered May 14 '15 at 06:05

shruti1810

3,920
2
16
28

score 2 · Accepted Answer · answered May 14 '15 at 07:09

when I scan the barcode it is add extra spaces in the cells and the result are not showing up as expected.

The idea is not to trim all the cells later but trim the bar code entry at the time of scanning. Is this what you want? Put this in the code area of the relevant sheet. I am assuming that the bar code will be scanned in Col B to E.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Target.Cells.Count > 1 Then Exit Sub

    Application.EnableEvents = False

    '~~> Assuming that the bar code is scanned in B to E
    '~~> If it is Just one column like B then change
    '~~> The code below to
    '~~> If Not Intersect(Target, Columns("B:B")) Is Nothing Then    
    If Not Intersect(Target, Columns("B:E")) Is Nothing Then
        Target.Value = Trim(Target.Value)
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Yes The idea is to trim the bar code entry at the time of scanning, thank you - you got it. — 0m3r, May 14 '15 at 07:21
For Col A it becomes `If Not Intersect(Target, Columns(1)) Is Nothing Then` — Siddharth Rout, May 14 '15 at 07:41
Another [Thing](http://stackoverflow.com/questions/11366823/how-to-wrap-excel-cursor-to-keep-it-within-a-specific-range) which may be of use to you if you are scanning barcodes :P — Siddharth Rout, May 14 '15 at 08:15

Remove extra spaces from cells in column

2 Answers2