Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim text and split across three columns | Excel Discussion (Misc queries) | |||
A Simple Trim | Excel Discussion (Misc queries) | |||
TRIM FUNCTION...how to? | Excel Worksheet Functions | |||
Text to Columns or TRIM? | Excel Discussion (Misc queries) | |||
Parse (TRIM) File Name into seperate columns | Excel Programming |