Excel: Difference between revisions
From Leo's Notes
This page was last edited on 30 December 2021, at 02:08.
Created page with " == Tasks == === Trim Cell Spaces === If cells contain extraneous leading or trailing spaces and you wish to trim each cell's content: # Select the range # Hit {{code|Alt+F11}..." |
m Added categories and navbox |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 3: | Line 3: | ||
=== Trim Cell Spaces === | === Trim Cell Spaces === | ||
If cells contain extraneous leading or trailing spaces and you wish to trim each cell's content: | If cells contain extraneous leading or trailing spaces and you wish to trim each cell's content: | ||
# Select the range | # Select the cell range you wish to trim | ||
# Hit {{code|Alt+F11}}, | # Hit {{code|Alt + F11}}. In the VB window, insert a new module: {{code|Insert -> Module}}. | ||
# Define a new module with the following code: {{highlight|lang=vbscript|code= | # Define a new module with the following code: {{highlight|lang=vbscript|code= | ||
Sub TrimCellSpace() | Sub TrimCellSpace() | ||
On Error Resume Next | |||
Dim Rng As Range | Dim Rng As Range | ||
Dim WorkRng As Range | Dim WorkRng As Range | ||
| Line 17: | Line 18: | ||
End Sub}} | End Sub}} | ||
# Hit {{code|F5}} and run against the selected range. | # Hit {{code|F5}} and run against the selected range. | ||
{{Navbox Windows}} | |||
[[Category:Windows]] | |||
Latest revision as of 02:08, 30 December 2021
Tasks
Trim Cell Spaces
If cells contain extraneous leading or trailing spaces and you wish to trim each cell's content:
- Select the cell range you wish to trim
- Hit
Alt + F11. In the VB window, insert a new module:Insert -> Module. - Define a new module with the following code:
Sub TrimCellSpace() On Error Resume Next Dim Rng As Range Dim WorkRng As Range Set WorkRng = Application.InputBox("Range", "Set Range", Application.Selection.Address, Type:=8) For Each Rng In WorkRng Rng.Value = VBA.Trim(Rng.Value) Next End Sub
- Hit
F5and run against the selected range.