Delete String Element

[John]

Hi there,

I've put together a procedure that deletes the first letter in each string
in a column of data. It works, but I can't help feel there's a more
efficient way of doing this. For example, is there a way of just editing
the string itself rather than taking a virtual copy and then replacing the
original as I've done here?

All comments most welcome.

Thanks

John


Sub RemoveFirstLetterInList()
'Deletes first letter in string per cell in list
Dim rgListItem As Range
Dim newStr As String
Dim ll As Integer
Dim r As Integer
Dim c As Integer
Dim totalStr As String

'Check user selects cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of list selected?", Buttons:=vbYesNo
+ vbQuestion)
If Answer = vbNo Then Exit Sub

r = ActiveCell.Row
c = ActiveCell.Column

Do
Set rgListItem = Cells(r, c)
If IsEmpty(Cells(r, c)) Then Exit Do
totalStr = rgListItem.Value
ll = Len(totalStr) - 1
newStr = Right(totalStr, ll)
rgListItem.Value = newStr

r = r + 1
Loop

Cells(r, c).Select

MsgBox "Finished"

End Sub

 

[Tom Ogilvy]

totalStr = rgListItem.Value
ll = Len(totalStr) - 1
newStr = Right(totalStr, ll)
rgListItem.Value = newStr


could be

rgListItem.Value = Right(rgListItem.Value, _
len(rgListItem.value)-1)

But your still doing pretty much the same thing.

 

[Chip Pearson]

You could change the following three lines

ll = Len(totalStr) - 1
newStr = Right(totalStr, ll)
rgListItem.Value = newStr

to one line

rgListItem.Value = Mid(totalStr,2)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[John]

Great. Thanks Tom and Chris. Its good for my learning!

Best regards

John