直接贴代码,方便大家学习。
'清除所有sheet页中单元格两端的空格
Public Sub trimCell()
Dim sheetCount, columnCount, rowCount, tempRow, tempCol
Dim rng As Range
'获取本excel的sheet的数量
sheetCount = ThisWorkbook.Sheets.count
For i = 1 To sheetCount
ThisWorkbook.Sheets(i).Select
'sheet原始选择的单元格行号和列号
tempRow = Selection.Row
tempCol = Selection.Column
'获取当前数据区域的行号和列号
columnCount = ActiveSheet.UsedRange.Columns.count
rowCount = ActiveSheet.UsedRange.Rows.count
'将数据区域置于选择区域
Range(Cells(1, 1), Cells(rowCount, columnCount)).Select
'遍历数据区域每个单元格
For Each rng In Selection
rng.Value = Trim(rng.Value)
Next
'还原单元格选择区域
Cells(tempRow, tempCol).Select
Next
End Sub
操作步骤(基于excel2013)
1.调用vba的编辑器
2 粘贴代码,运行