How do I make a macro remove last char only in a cell?

[Martin Richecoeur]

Here is my macro code but it doesn't just remove the last char it uses the
cell's absolute end result after a char has been removed instead of relative
as clicked!? EG: ActiveCell = "1" where as future cells have multiple 
and do not start with 1 but it changes them all to "1" !?
Sub DeadCHAR()
'
' DeadCHAR Macro
' Macro recorded 25/10/2003 by ¤notsovilefriend
'
' Keyboard Shortcut: Ctrl+d
'
ActiveCell.FormulaR1C1 = "1,"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

 

[Don Guillett]

Not sure of what you were doing but if you want the subject line,try this
ActiveCell.value=left(activecell,len(activecell)-1)

 

[Michael Bednarek]

Here is my macro code but it doesn't just remove the last char it uses the
cell's absolute end result after a char has been removed instead of relative
as clicked!? EG: ActiveCell = "1" where as future cells have multiple 
and do not start with 1 but it changes them all to "1" !?
Sub DeadCHAR()
'
' DeadCHAR Macro
' Macro recorded 25/10/2003 by ¤notsovilefriend
'
' Keyboard Shortcut: Ctrl+d
'
ActiveCell.FormulaR1C1 = "1,"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Try:
ActiveCell = Left(ActiveCell, Len(ActiveCell) - 1)

You should not apply this to cells with formulae.

Isn't Ctrl+d an unfortunate choice? I find it extremely useful in its
default meaning of "Fill Down". I'd recommend Ctrl+Shift+D.

Subject change:
What surprised me most was that Excel doesn't change the keyboard
shortcuts on its menus when the user changes the shortcuts. If a user
assigns Ctrl+d to a macro, the Edit/Fill/Down menu still shows "Ctrl+D" -
which is wrong anyway: it should show "Ctrl+d". Very sloppy programming.
MS Word and many other programs do not behave that way.

 

[Tom Ogilvy]

Another possibility

Sub DeadCHAR()
ActiveCell.Formula = Application.Trim( _
Application.Clean(ActiveCell.Value))
ActiveCell.Offset(1,0).Select
End Sub

or if you want to process multiple cells which are all selected

Sub DeadCHAR1()
Dim cell as Range
For each cell in Selection
if not cell.HasFormula then _
Cell.Formula = Application.Trim( _
Application.Clean(Cell.Value))
Next
End Sub

Remove ApplicationTrim( ) if you don't want redundant spaces removed.

This doesn't clean up characters like character 160 which is often brought
down from web pages - if that is an issue, post back.

 

[Kent]

Hi

Try this:

Sub RemoveLastChar()
Dim strContent As String
strContent = Left(ActiveCell, (Len(ActiveCell) - 1))
ActiveCell = strContent
End Sub

Kent Schederin

 

[Martin Richecoeur]

Thank you all guys! I have yet to subsumed each suggestion entirely but
thought it best to respond whilst the thread was still alive so thank you to
Don Guillett, Michael Bednarek, Tom Ogilvy, and Kent Schederin! I'm sure I
will be able to extract the required code from you reply posts. CHEERS GUYS!

 

[b&s]

This doesn't clean up characters like character 160 which is often brought
down from web pages - if that is an issue, post back.

Tom, please, tell me how to solve such problem!


--
regards/pozdrav!
Berislav

***************************************
ROT13 - email address (e-mail address removed)

 

[Dave Peterson]

One way:

Just select your range and run this:

Sub fixme()
Selection.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

 

[Tom Ogilvy]

you can also do

Sub DeadCHAR()
ActiveCell.Formula = Application.Substitute(Application.Trim( _
Application.Clean(ActiveCell.Value)),chr(160),"")
ActiveCell.Offset(1,0).Select
End Sub



Sub DeadCHAR1()
Dim cell as Range
For each cell in Selection
if not cell.HasFormula then _
Cell.Formula = Application.Substitute(Application.Trim( _
Application.Clean(Cell.Value)),chr(160),"")
Next
End Sub

if you want to do the rest. Or you could add Dave's code as a separate
step.

 

[David McRitchie]

Hi Martin,
The CHAR(160) that you get from text copied from HTML is the
non-breaking space character (&nbsp in other languages it is
called Required Blank (RBL). I would suggest replacing all
CHR(160) with a space and then trimming, that takes care of it
as a last character. The TRIM function in VBA differs from the
TRIM function in Excel. In Excel TRIM reduces consecutive spaces
to a single space so you will see application.TRIM in the macro
instead of TRIM but you can change to match your preferences.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Simply replacing CHAR(160) by an empty string could get you
into trouble, if between two words. Likewise removing the
last character regardless of what it is could get you into trouble.

 

[b&s]

Tom & Dave, thank you for responding...

Tom, if I have Chr(32) + Chr(160) as last characters in a cell, then I have
to run macro DeadCHAR twice. Why?

--
regards/pozdrav!
Berislav

***************************************
ROT13 - email address (e-mail address removed)

you can also do

Sub DeadCHAR()
ActiveCell.Formula = Application.Substitute(Application.Trim( _
Application.Clean(ActiveCell.Value)),chr(160),"")
ActiveCell.Offset(1,0).Select
End Sub



Sub DeadCHAR1()
Dim cell as Range
For each cell in Selection
if not cell.HasFormula then _
Cell.Formula = Application.Substitute(Application.Trim( _
Application.Clean(Cell.Value)),chr(160),"")
Next
End Sub

if you want to do the rest. Or you could add Dave's code as a separate
step.

 

[Tom Ogilvy]

Because Trim has no reason to remove Chr(32) unless it is on the end - and
in the way I structured the function, it doesn't appear on the end until it
has been run one time. this will only require one run:

Sub DeadCHAR()
ActiveCell.Formula = Application.Trim(Application.Substitute( _
Application.Clean(ActiveCell.Value), Chr(160), " "))
ActiveCell.Offset(1, 0).Select
End Sub

It also puts in a space for the 160 rather than nothing. Change it back if
you prefer not putting in the space. It would only have an effect if the 160
was interior in the string.

--
Regards,
Tom Ogilvy

Tom & Dave, thank you for responding...

Tom, if I have Chr(32) + Chr(160) as last characters in a cell, then I have
to run macro DeadCHAR twice. Why?

--
regards/pozdrav!
Berislav

***************************************
ROT13 - email address (e-mail address removed)

 

[b&s]

Tom, thank you for your help and explanation.

--
regards/pozdrav!
Berislav

***************************************
ROT13 - email address (e-mail address removed)

 

[Tom Ogilvy]

David,
You might want to name your page something other than "Join". People might
think you are directing them to a pay site.

 

[David McRitchie]

Not pay to play. No fees. Just code to join cells and other rearrangements.
Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/zoink.htm#trimall