macro to rearrange data

[King Nothing]

Hey all

I have a set of data in excel that I need rearranged so I can easily import it into access. The excel table is in the current format

Data type ______Unit______Jan 06______Feb 06______Mar 06_____ (...etc)
Coal consumed__tonnes____xxxxx________xxxxx________xxxx______(...etc)
Coal energy_____GJ_______xxxxx________xxxxx________xxxx______(...etc)
Electricity______kwh______xxxxx________xxxxx________xxxx______(...etc)
Natural Gas_____GJ_______xxxxx________xxxxx________xxxx______(...etc)
Slab production__tonnes___xxxxx________xxxxx________xxxx______(...etc)
(...etc)

This is an example table that could be of any date range and any number of Data types. I need to write a macro that will take a table of any size in this format, and change it into a table of this format

Data type________Value____Unit_______Date
Coal consumed____xxxx_____tonnes____Jan 06
Coal consumed____xxxx_____tonnes____Feb 06
Coal consumed____xxxx_____tonnes____Mar 06
Coal energy_______xxxx_____GJ_______Jan 06
Coal energy_______xxxx_____GJ_______Feb 06
Coal energy_______xxxx_____GJ_______Mar 06
(...etc)

I have got the following macro from a previous source

Sub test()
Dim a, i As Long, ii As Integer, b(), n As Long
With Range("a1")
With .CurrentRegion
a = .Value
.ClearContents
End With
ReDim b(1 To (UBound(a, 1) - 1) * (UBound(a, 2) - 2), 1 To 4)
For i = 2 To UBound(a, 1)
For ii = 3 To UBound(a, 2)
n = n + 1
b(n, 1) = a(i, 1): b(n, 3) = a(i, 2): b(n, 4) = a(1, ii)
Next
Next
.Resize(n, 4).Value = b
End With
End Sub

The problem with this is that it doesn't carry over the data (xxxx), it just leaves a blank column. Any help is greatly appreciated!

[mikerickson]

Try adding one line

Please Login or Register  to view this content.

I think that will do it.

[King Nothing]

Fantastic! Thanks, I've been trying to figure this one out for a couple of hours now.

[mikerickson]

You're welcome to the code.

When you cross post, please post the link. All sites, including this one, strongly request this courtesy from their users.

An independent duplicate of this solution is at http://www.mrexcel.com/board2/viewtopic.php?t=304904

[King Nothing]

My apologies, I will keep this in mind in the future. As explained on the other site, I thought the forum had shut down as it was not working at my end, and I could not get the link to the original post. Thanks again.