simple edit

[excelnut1954]

I've done this before... but can't find the correct syntax...

In current cell. I want to
Edit, Home (to 1st character), delete first 4 characters, then down to
the next cell, then loop until there is an empty cell.
Thanks
j.o.

 

[JE McGimpsey]

One way:

Do Until IsEmpty(ActiveCell.Value)
With ActiveCell
.Value = Mid(.Text, 5)
.Offset(1, 0).Activate
End With
Loop

 

[Guest]

Sub clipum()
Set r = ActiveCell
For i = 1 To 65536
r.Value = Right(r.Value, Len(r.Value) - 4)
Set r = r.Offset(1, 0)
If r.Value = "" Then Exit Sub
Next
End Sub

 

[Guest]

Something like this should be close (untested)...

dim rng as range

set rng = Range("A1")
do while rng.value <> ""
rng.value = mid(rng.value, 5, 256)
loop

 

[excelnut1954]

Something like this should be close (untested)...

dim rng as range

set rng = Range("A1")
do while rng.value <> ""
rng.value = mid(rng.value, 5, 256)
loop

--
HTH...

Jim Thomlinson






- Show quoted text -

Thanks to all who responded.
Jim, so I can save examples for later reference, can you please
rewrite the rng.value line so that all is deleted from the cell EXCEPT
the 1st 5 characters.

rng.value = mid(rng.value, 5, 256)
This way, I'll better understand the syntax.
Thanks again
j.o.

 

[Guest]

So you only want to keep the left 5 characters... try this

rng.value = left(rng.value, 5)