simple edit
 

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.

simple edit
 

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.

simple edit
 

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.

simple edit
 

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.

simple edit
 

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.

simple edit
 

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.