On Thu, 31 Jul 2008 13:10:07 -0700, EmmieLou
wrote:

A B C D E F
1 a 1 f 6 h 8
2 b 2 g 7
3 c 3
4 d 4
5 e 5

Sub TransData()

Dim i As Long, j As Long
Dim vaData As Variant
Dim rOutput As Range
Dim lCount As Long

vaData = Sheet1.UsedRange.Value2
Set rOutput = Sheet1.Cells(Sheet1.UsedRange.Rows.Count + 10, 1)

For i = LBound(vaData, 2) To UBound(vaData, 2) Step 2
For j = LBound(vaData, 1) To UBound(vaData, 1)
If Len(vaData(j, i)) 0 Then
rOutput.Offset(lCount, 0).Value = vaData(j, i)
rOutput.Offset(lCount, 1).Value = vaData(j, i + 1)
rOutput.Offset(lCount, 2).Value = "Location " & Chr$((i \ 2)
+ 65)
lCount = lCount + 1
End If
Next j
Next i

End Sub

Watch for line wrap.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com