How to Transform Blocks from Horizontal Data to Vertical Data (example attached)

Hi All,
It's been a while since I needed to use VBA.. I'm embarrassed as this may be really simple but I've tried and failed so far...
In the example attached I need to change format from what's on Sheet1 to what's on Sheet2.
I'm working with a font and how it is displayed using a Microcontroller (PIC), so there's 256 records * 9 columns by 9 rows (I need 1 row space between)
I tried OFFSET as it seemed obvious, also INDEX and TRANSPOSE (but it mirrors).. no good, also found a great bit of code for rotate (8*8) but couldn't alter it to repeat...
anyone help?
** this all seemed so easy when I was younger, now at 64 the synapses are just slower..

Do you mean?

Please Login or Register  to view this content.

Thanks jindon... that works for me. Can you expand on how this works?
I can understand the "SpecialCells" but am struggling with the "IIf" part.

IIf function in vba works like If function in worksheet.

Returns one of two, IIf(Condition, When true, When false)
Could be rewrite like

Please Login or Register  to view this content.

So, when loop counter = 1, it returns 1, otherwise 3.
It is used for the row to be pasted in sheet2.
End(xlUp) refers to the last non empty cell, but when the column is blank, it returns 1.
End(xlUp)(1) actually means End(xlUp).Cells(1, 1) (RowRef, ColRef from the cell itself and both optional, 1, 1 is the default).
End(xlUp)(3) will give you 2 rows below after the last non empty cell, so it gives you 1 blank between the data.

One thing you should be very carefull using IIf function is, unlike If in worksheet function, it evaluates both True, False completely.

Therefore you can not used it e.g IIf(Range Is Nothing, Empty, Range.Value), because if Range is Nothing Range.Value is impossible.

HTH.