如何一次从Excel工作表的所有单元格中删除前导和尾随空格 [英] How to remove leading and trailing spaces from all cells of a excel sheet at once

问题描述

我有一个Excel工作表，其中包含很多数据，前导和尾随空格.

I have a excel sheet which contains lots of data with leading and trailing spaces.

使用TRIM()手动删除这些空格会花费很多时间.

Manually removing these spaces with TRIM() takes lot of time.

如何有效地做到这一点.

How can I do this efficiently.

推荐答案

要有效地使用变量数组，例如:

To do this efficiently use a variant array, something like:

Sub VBATrim()
    Dim rng1 As Range
    Dim rngArea As Range
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCalc As Long
    Dim X()


    On Error Resume Next
    Set rng1 = Application.InputBox("Select range for the replacement of non-number", "User select", Selection.Address, , , , , 8)
    If rng1 Is Nothing Then Exit Sub
    On Error GoTo 0


   'Speed up the code by turning off screenupdating and setting calculation to manual
   'Disable any code events that may occur when writing to cells
    With Application
        lngCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'Test each area in the user selected range

    'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
    For Each rngArea In rng1.Areas
        'The most common outcome is used for the True outcome to optimise code speed
        If rngArea.Cells.Count > 1 Then
           'If there is more than once cell then set the variant array to the dimensions of the range area
           'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
            X = rngArea.Value2
            For lngRow = 1 To rngArea.Rows.Count
                For lngCol = 1 To rngArea.Columns.Count
                    'replace the leading zeroes
                    X(lngRow, lngCol) = Trim(X(lngRow, lngCol))
                Next lngCol
            Next lngRow
            'Dump the updated array sans leading zeroes back over the initial range
            rngArea.Value2 = X
        Else
            'caters for a single cell range area. No variant array required
            rngArea.Value = Trim(rngArea.Value)
        End If
    Next rngArea

    'cleanup the Application settings
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
    End With

End Sub

这篇关于如何一次从Excel工作表的所有单元格中删除前导和尾随空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！