本文介绍了如何一次从Excel工作表的所有单元格中删除前导和尾随空格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个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工作表的所有单元格中删除前导和尾随空格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!