Macro help - use to work...now getting an error

[lwilt]

I'm using a macro to transpose some data in excel. Attached is an example of the file. As you can see in it when you get to the last item in the ex the 3rd column is not sorted correctly. So I tried sorting the file before running the macro doing the sort on the first 3 columns in order to keep the grouping the same before I ran the macro but by doing that I'm not getting an error.

Please help

here is the macro:

Sub TransposeQandP()

  Dim X As Long, Ar As Range

  With Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlBlanks)

  For Each Ar In .Areas

  For X = 1 To Ar.Count

  Ar(1).Offset(-1, Cells(Ar(1).Offset(-1).Row, Columns.Count).End(xlToLeft). _

  Column).Resize(, 2).Value = Ar(1).Offset(X - 1, 2).Resize(, 2).Value

  Next

  Next

  .EntireRow.Delete

  End With

End Sub

 

[Luke M]

I'm not sure what the problem/question is. With your sample file, macro runs and data appears to be re-arranged correctly. Can you elaborate on what is not working?