Transpose

First my apologies for the style, I got started on CBM Pet basic and haven't lost the spaghetti coding bug yet.

This works on your current data set and should work no matter how you expand the cols and rows, although I didn't test it fully.

A few points

1. the original data should be on a worksheet called "ORIGINAL" and the output sheet created as "output"...basically just as they are now.
2. The header "Blank" must be in the relative place as it appears in your original.
3. To achieve your exact result you should delete your unwanted cols in the output like "Area" or whatever.
That's about it.

Sub trans()



Dim col As Integer

Dim origrow As Integer

Dim outrow As Integer

Dim datcol As Integer



outrow = 1



label1:

col = col + 1

If Worksheets("ORIGINAL").Cells(1, col) = "Blank" Then GoTo label2

datcol = col

Worksheets("Output").Cells(1, col + 1) = Worksheets("ORIGINAL").Cells(1, col)

GoTo label1

label2:

col = col + 1

origrow = 1

label3:

outrow = outrow + 1

origrow = origrow + 1

Worksheets("Output").Cells(outrow, 1) = Worksheets("ORIGINAL").Cells(1, col)

For x = 1 To (datcol)

Worksheets("Output").Cells(outrow, x + 1) = Worksheets("ORIGINAL").Cells(origrow, x)



Next x

Worksheets("Output").Cells(outrow, x + 1) = Worksheets("ORIGINAL").Cells(origrow, col)

If Worksheets("ORIGINAL").Cells(origrow + 1, col) <> 0 Then GoTo label3

If Worksheets("ORIGINAL").Cells(1, col + 1) <> 0 Then GoTo label2



End Sub

Brief explanation how it works.
The macro scans along the header row writing all it finds into the output until it hits "Blank"
It will then take the value of the next col (Date) and place it in col A row1 and populate the rest of the row.
It will then check to see if the next row has data, etc until cell is empty, it will then check to see if the next date header isn't empty and carry on.

Hope it does the trick, but I'm sure it wouldn't need much tinkering to sort it.
best of luck

col

Just read your code pike and although you put me to shame (not hard really) I think the header "Blank" is quite important to the scheme as I think columns could be added or taken away, thus making "Blank" an "end of headers" marker, dates now follow.

sorry my knowledge of VBA is very limited and most of your code goes straight over my head.

My apologies for doubting you.

Hello Guys,

Thanks for the response.

All of the codes worked fine for the given workbook but they are not completely generic.

I have coded it to be generic to any kind of data with any position of the blank column (except last).

But there is just one issue,

The code takes a hell lot of time to execute when inserting a row.

Pike and Col12345, please do help me make this thing a bit quicker.

Please Login or Register  to view this content.

sorry to disappoint, but my VBA skills are limited, pike and TMshucks are clearly your best bet.