Reformatting an excel 2007 file

Try this:

Option Explicit

Sub ReformatData()
'JBeaucaire   3/12/2010
Dim LR As Long, RNG As Range

LR = Range("A" & Rows.Count).End(xlUp).Row

    With Range("M5:M" & LR)
        .FormulaR1C1 = _
            "=IF(ISNUMBER(RC1), INDEX(C2, MATCH(""CHAINAGE"", R1C1:RC1, 1)) & ""_"" & RC1, """")"
        .Value = .Value
    End With

    With Range("A1:A" & LR)
        .SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete xlShiftUp
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete xlShiftUp
        .Value = .Offset(0, 12).Value
    End With

Columns("M:M").ClearContents
End Sub

==========
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

Well, shucks, I was all happy to be able to do it without "looping"...oh well. Here you go:

Option Explicit

Sub ReformatData()
'JBeaucaire   3/12/2010
Dim LR As Long, Rw As Long, Shp As Shape
Dim MyKey As String, MyChn As String
Application.ScreenUpdating = False

MyKey = "CHAINAGE"
    
    With ActiveSheet
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        Rw = Cells.Find(MyKey, After:=.[A3], LookIn:=xlValues, LookAt:=xlWhole).Row
        
        Do
            If UCase(Trim(Cells(Rw, "A"))) = "CHAINAGE" Then MyChn = "_" & Cells(Rw, "B")
            If IsNumeric(Cells(Rw, "A")) And Cells(Rw, "A") > 0 Then Cells(Rw, "A") = Cells(Rw, "A") & MyChn
            Rw = Rw + 1
        Loop Until Rw > LR
    End With


On Error Resume Next
    Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete xlShiftUp
    Range("C1:C" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete xlShiftUp
    Range("C1:C" & LR).SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete xlShiftUp
    Rows(1).Delete xlShiftUp

    For Each Shp In ActiveSheet.Shapes
        Shp.Delete
    Next Shp

Application.ScreenUpdating = True
End Sub

1) Please edit that post above...either remove the code or encase it in code tags required by forum rules (example in my signature)

2) I noticed I create a variable and forgot use it, so a final correction might be this:

If UCase(Trim(Cells(Rw, "A"))) = MyKey Then MyChn = Cells(Rw, "B") & "_"
If IsNumeric(Cells(Rw, "A")) And Cells(Rw, "A") > 0 Then Cells(Rw, "A") = MyChn & Cells(Rw, "A")