Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that deletes the last character in a cell
I have an 18,000 record database and every entry is a name with the
letter A added to the end. for example: Lastname, firstname A . I need a macro that will open the cell, backspace the "A" out of there, close the cell for editing, moves down one record or line and loops back to editing the cell and removing the A. This was simple in Lotus but I'm not a programmer and I really don't want to spend 20 hours hitting, F2, Backspace, enter. Does anyone have a simple solution. I do know how to set up a macro in Excel but that's about it. Thanks ...TJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that deletes the last character in a cell
How about this?
Sub TryThis() Dim cell As Range For Each cell In ActiveSheet.UsedRange If VarType(cell) = vbString Then cell = Left(cell, Len(cell) - 1) End If Next cell End Sub James TJ wrote: I have an 18,000 record database and every entry is a name with the letter A added to the end. for example: Lastname, firstname A . I need a macro that will open the cell, backspace the "A" out of there, close the cell for editing, moves down one record or line and loops back to editing the cell and removing the A. This was simple in Lotus but I'm not a programmer and I really don't want to spend 20 hours hitting, F2, Backspace, enter. Does anyone have a simple solution. I do know how to set up a macro in Excel but that's about it. Thanks ...TJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that deletes the last character in a cell
TJ
The following assumes records start at row 2 (allowing header in row 1) and that the names with the A on the end are in column A. Sub Test() For Each c in Range("A2:A" & Range("A65536").End(xlUp).Row) c.Value = Left$(c.Value,Len(c.Value)-1) Next c End Sub Stick this in a standard module in the workbook containing the data (Alt+F11 to get the VBE open, right click on the workbook name in the Projects pane (top left) and go InsertModule and post the code in the big white space that opens). Back in the spreadsheet, on the sheet with the records, go ToolsMacroMacros and select Test from the list and click Run. Hope this helps! Richard TJ wrote: I have an 18,000 record database and every entry is a name with the letter A added to the end. for example: Lastname, firstname A . I need a macro that will open the cell, backspace the "A" out of there, close the cell for editing, moves down one record or line and loops back to editing the cell and removing the A. This was simple in Lotus but I'm not a programmer and I really don't want to spend 20 hours hitting, F2, Backspace, enter. Does anyone have a simple solution. I do know how to set up a macro in Excel but that's about it. Thanks ...TJ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that deletes the last character in a cell
Richard, would you be so kind as to explain your use of the $ character
in the term Left$. It appears to be a typecasting character from my old BASIC days. What are the implications of using the $ character with Left at run-time? TIA, James c.Value = Left$(c.Value,Len(c.Value)-1) RichardSchollar wrote: TJ The following assumes records start at row 2 (allowing header in row 1) and that the names with the A on the end are in column A. Sub Test() For Each c in Range("A2:A" & Range("A65536").End(xlUp).Row) c.Value = Left$(c.Value,Len(c.Value)-1) Next c End Sub Stick this in a standard module in the workbook containing the data (Alt+F11 to get the VBE open, right click on the workbook name in the Projects pane (top left) and go InsertModule and post the code in the big white space that opens). Back in the spreadsheet, on the sheet with the records, go ToolsMacroMacros and select Test from the list and click Run. Hope this helps! Richard TJ wrote: I have an 18,000 record database and every entry is a name with the letter A added to the end. for example: Lastname, firstname A . I need a macro that will open the cell, backspace the "A" out of there, close the cell for editing, moves down one record or line and loops back to editing the cell and removing the A. This was simple in Lotus but I'm not a programmer and I really don't want to spend 20 hours hitting, F2, Backspace, enter. Does anyone have a simple solution. I do know how to set up a macro in Excel but that's about it. Thanks ...TJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro deletes row in range, macro then skips the row moved up | Excel Discussion (Misc queries) | |||
Macro that deletes rows from cell containing End to end of data. | Excel Discussion (Misc queries) | |||
Macro that deletes every third row....+ | Excel Discussion (Misc queries) | |||
Macro that deletes certain rows and not others | Excel Programming | |||
hitting spacebar in excel deletes last character entered. | New Users to Excel |