Delete String Element
 

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

Delete String Element
 

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.

--
Regards,
Tom Ogilvy


"John" wrote in message
...
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

Delete String Element
 

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" wrote in message
...
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

Delete String Element
 

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

Best regards

John
"John" wrote in message
...
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