I often receive large Excel-sheets in which data has been inserted via copy and paste. Usually a lot of the cells contain whitespaces at the beginning and the end before / after the actual value.
I made a VBA-script for to get rid of these whitespaces.
' Removes whitespaces at the
' beginning and the end
' of all cell-values.
Public Sub trimCells()
Dim lastRow As Long
Dim lastColumn As Long
Dim row As Long
Dim column As Long
On Error GoTo errorHandler
' Find() == VBA-equivalent to
' the find-menue in the Excel-GUI.
' What:="*" => Matches EVERY content.
' Start in the bottom-right corner &
' search from there through the rows
' upward.
lastRow = Cells.Find(What:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).row
' Search in the last row with content for most
' right cell with a value.
lastColumn = Cells(lastRow, Columns.Count).End(xlToLeft).column
' Go through the detected range &
' apply the trim-function on every cell.
For row = 1 To lastRow
For column = 1 To lastColumn
Cells(row, column).Value = _
Trim(Cells(row, column).Value)
Next
Next
Exit Sub
errorHandler:
MsgBox "Error has occurred.", _
vbCritical, "Error!"
End Sub
I apply the trim-function on every
cell-value. So I ask myself:
What is better?
Doing unnecessary executions of the trim-function or checking if it has to be applied? Checking would cost me an additional if-then.
UPDATE
The above algorithm contains an error.
Concerning a table with the following structure:
First | Second |
Alpha | Beta | Gamma |
One |
The result would be:
- lastRow: 3
- lastColumn: 1
Reason: It searches in the last row for the most right column. But what is needed is the most right column overall.
Now I determine lastRow and lastColumn this way:
lastRow = Range("A1").SpecialCells(xlCellTypeLastCell).row
lastColumn = Range("A1").SpecialCells(xlCellTypeLastCell).column