How to remove spaces between cells?

remove extra spaces from cells in column

• I wrote the following code to do inventory scanning bar-codes but for some reason when I scan the bar-code it is add 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

• Answer:

  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