HELP using simple TRIM function on relative columns
 

Relatively infrequent VBA programmer needs help writing small ap (this
will set me in the right direction with so many others).

I would like to pick any cell in a worksheet, and have it run this Macro
for that column (so that everything is relative to the column selected
and that the row selected is ignored).

As an example using the TRIM function, I want to replace the column
selected with the all the values trimmed.

Step 1:

Determine the column selected (and store it somewhere?)

Step 2:

Insert two columns after the selected one. Put the TRIM value of each
entry in the original column in the the first new column, copy
paste-values into the second new column (so the original can be erased).

Step 3: Erase the original column and the first new column, leaving only
the new trimmmed column. (If you want to save the initial row
value you could return the pointer to the original cell (optional)).

Thanks!

HELP using simple TRIM function on relative columns
 

Skip

The following code will run through the column of the active cell, from the top
to the last used row, and replace the contents of each cell with the =trim() of
the contents.

Sub Macro1()

Application.ScreenUpdating = False

col = ActiveCell.Column

For i = 1 To Cells.SpecialCells(xlLastCell).Row
Cells(i, col).Value = Application.Trim(Cells(i, col))
Next i

End Sub

You can set it to run automatically when triggered by a workbook event or
another trigger or your choice. As written tt starts at the top of the column.
It may be more efficient to start at the active cell (start the i loop at
activecell.row rather than 1) or somewhere else but that depends on your data.
If you have a whole lot of rows to effect the following the steps you layed out
in your post may be more efficient since you can manipulate a whole column at
once.

Good luck.

Ken
Norfok, Va

HELP using simple TRIM function on relative columns
 

Skip,

Another approach

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
Dim cLastRow As Long

If Target.Column = 1 Then
On Error GoTo ws_exit
Application.EnableEvents = False
cLastRow = Me.Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Me.Range("A1").Resize(cLastRow, 1)
cell.Value = Trim(cell.Value)
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Skip Albertson" wrote in message
news:Pine.LNX.4.60.0412271247070.7273@tsunami...
Relatively infrequent VBA programmer needs help writing small ap (this
will set me in the right direction with so many others).

I would like to pick any cell in a worksheet, and have it run this Macro
for that column (so that everything is relative to the column selected
and that the row selected is ignored).

As an example using the TRIM function, I want to replace the column
selected with the all the values trimmed.

Step 1:

Determine the column selected (and store it somewhere?)

Step 2:

Insert two columns after the selected one. Put the TRIM value of each
entry in the original column in the the first new column, copy
paste-values into the second new column (so the original can be erased).

Step 3: Erase the original column and the first new column, leaving only
the new trimmmed column. (If you want to save the initial row
value you could return the pointer to the original cell (optional)).

Thanks!