Delete String Element

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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.
 
You could change the following three lines
ll = Len(totalStr) - 1
newStr = Right(totalStr, ll)
rgListItem.Value = newStr
Click to expand...

to one line

rgListItem.Value = Mid(totalStr,2)


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