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.

One way:

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

In article .com,
excelnut1954 wrote:

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.

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

--
Gary''s Student - gsnu2007a


"excelnut1954" wrote:

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.

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


"excelnut1954" wrote:

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.

On Oct 30, 11:07 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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



"excelnut1954" wrote:
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.- Hide quoted text -

- 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.

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

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

Jim Thomlinson


"excelnut1954" wrote:

On Oct 30, 11:07 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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



"excelnut1954" wrote:
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.- Hide quoted text -

- 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.