Resolved >> Macro to edit multiple rows of text

I used the macro editor to create a "simple" macro to edit 7 rows of text. The steps were basic and simple - F2 key, backspace about 25 times, and down arrow then repeat these 3 steps 6 more times. The macro I got gave me the result of the very first cell as I was recording the macro.

I think I remember the old Lotus 1-2-3 macro text - {edit}{backspace}{down}. Wish it were still that simple.

Anyone got any clues?

If the 7 cells are A1:A7, try this:

Sub EditRows()
    Dim Cell As Range, Rng As Range
    Set Rng = Range("A1:A7")
    For Each Cell In Rng
        Cell = Left(Cell, Application.Max(0, Len(Cell) - 25))
    Next Cell
End Sub

You are simply using the Left function, same as you would as a formula. Also, you need to use the Max worksheet function, in order to avoid a negative number, which would create an error.

HTH

Jason

Thanks Jason! I used to have a real good excel macro book but lost it in a fire a couple of years ago. Do you know any good titles?

Originally Posted by VBA Noob

Hi,

Walkenbach books always rate highly and come with demo discs.

Definitely! I am actually a self-taught VBA user, with only the use of this forum and Walkenbach's Excel 2003 Power Programming with VBA.

Perfect, thanks