Replacing last comma in range of cells

[Naoise]

I have a list of cells like

asdfc,
asdfs,
asdfwe,
sdfsf,

I have added a Module like so

Public Sub RemoveEndComma()

Dim rCell As Range

For Each rCell In Selection
rCell.Value = Left(rCell.Value, (Len(rCell.Value) - 1))
Next rCell

End Sub

but get an error about an invalid procedure call or argument. Being a newbie to this is it that I am referring to a range of values and not the cell value itself that is causing the error?

 

[GrahamHills]

You need to select the cells you want the procedure to modify, because the procedure is as follows:-

For Each rCell In Selection

 

[Naoise]

Does Selection not detect what cells I have highlighted when I run the Macro?

 

[GrahamHills]

Yes it does. You only need to select the cells and then run the macro. Your macro is syntaxically correct. I know that for sure because I have copied your code and run it behind a button

 

[SkipVought]

Hi,

One of the cells in your selection has NO LENGTH of text value.

For Each rCell In Selection
  If Len(rCell) > 0 Then _
    rCell.Value = Left(rCell.Value, Len(rCell.Value) - 1)
Next rCell

Skip,
[sub]
[red][/red]
[/sub]

 

[Loomah]

Hi
if all you are doing is removing commas and the commas only appear at the end of your text then the single line (ie there aren't any commas in the middle of your data you need to keep) the following will do the trick

Cells.Replace What:=",", Replacement:=""

this eliminates the need to loop through anything

this covers the whole sheet so if appropriate change cells to a more specific range eg columns(1).replace etc etc

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​