What am I missing in this code?

[Brutusar]

I am using the code pasted in below to transpose a range. An example range is shown in row 133 in the minisheet below. The code produces the result shown in row 7-15.
I am trying to "expand" the code so I can adjust the number of columns to be transposed, from minimum 2 cols to maximum 10. Any good ideas on how to do that best? ( I can't get it to work at all)

This is the code I am using:

Sub Transpose()
    Dim Ary As Variant, Nary As Variant
    Dim r As Long, nr As Long, c As Long
   
    Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
    ReDim Nary(1 To UBound(Ary) * 5, 1 To 4)
    For r = 1 To UBound(Ary)
        For c = 5 To UBound(Ary, 2)
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(r, 2)
            Nary(nr, 3) = Ary(r, c)
        Next c
    Next r
    Sheets("Sheet1").Range("M1").Resize(nr, 3).Value = Nary
End Sub

Example.xlsx
	G
7
Sheet1

 

[Fluff]

Do you have formulae in those cells?

 

[Brutusar]

No, they should be empty. Every time the code has run and entered in data for processing it is running a "Clear all" macro, deleting everything in these cell

 

[Fluff]

If it's deleting everything in those cells, then what you said in post#10 makes no sense.

 

[Brutusar]

Not isolated, but it is a multi-step process. Data is being written into a excel sheet using Python, and then multiple VBA codes are being run in an order, processing that code. This is a loop, so when the processing is done, all data (apart from the result that is copied into a new sheet, is cleared. Then new data is being written in by Python.

The problem is that when data is written in to Excel the second time (or any time following the first), it is supposed to be written in to a range starting at A1, but it does not. It start writing in data in the first row that was empty in the first loop. This causes the next macro to fail as it is difficult to forsee where the first row is.

 

[Fluff]

Either the cells are not empty, or there is a problem with your python code.