Excel: Difference between revisions
From Leo's Notes
This page was last edited on 15 April 2020, at 20:11.
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}..." |
|||
| 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() | ||
Revision as of 20:11, 15 April 2020
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() 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.