0

I am using some code I found on this site to transpose a section of a table

Copy Partial Table and Insert N-times, then Transpose second part of table

however I have found that it is not transposing the data beyond the last filled cell in the range being transposed, resulting in mis-alignments of the data in the resulting table. I believe it is due to the .End(xlUp) reference in the last line of the code.

tws.Cells(tws.Rows.Count, dataClmStrt + 2).End(xlUp).Offset(1).Resize(38, 1).Value = _
    Application.Transpose(rng.Offset(, dataClmStrt).Resize(, 38))

Is this the cause of the issue and if so can this term be replaced with a set value?

EDIT---------------------------------------------------

To add some more information I have a table with 4 reference columns and 38 data column

Columns A-D contain identifiers to make each line item unique, columns 1-38 contain data in accordance to the heading category of the column.

A B C D | 1 2 3 ... 38

Not all of the 38 category columns contain data, some are empty cells

I am using the code from the reference link verbatim, except have changed to 12s to 38s to reflect the number of columns and expanded the main Arr to 38.

The code will copy the data down the rows correctly, and add the main Arr IDs correctly, but doesn't transpose the 38 columns of data correctly.

Once it reaches a cell with data, it will add this data to the tws sheet, the return to data column 1 and begin again, but in the next row. (i.e. if it finds data in column 12 in row 1 of the original table, it will add that data to row 12 of the new table, but the start adding data from column 1 row 2 of the original table to row 13 of the new table)

I have found by adding a checksum column (column 39) it will transpose correctly. Once the code has run, I use two other subs to remove the blanks and the check sums.

I would like to be able to run the code without needing the check sum column, or if possible eliminate the blanks automatically without additional subs.

Hope this is clearer

Icaruim14
  • 51
  • 8
  • .End(xlUp) starts at the last row and goes up to the first populated cell it sees in that specified column. You'd either have to find the column with the most rows and specify that column, or possibly use tws.UsedRange.Rows.Count – pondersome Jul 31 '18 at 03:05
  • Thanks for the reply – Icaruim14 Jul 31 '18 at 03:14
  • For clarity every column has the same amount of rows. My table looks like the one in the linked example, however it has empty cells scattered throughout. – Icaruim14 Jul 31 '18 at 03:15
  • 1
    Scott Craner's code in the link you provided was designed around the OP's specific environment so you'd have to make all the necessary adjustments if your environment doesn't match the OP's. In short, yes, you can hard code cell references with set values. That's not usually the ideal approach. The one line of code in the question above isn't enough to provide you with much help. You'd have to post the code and a better representation of the data you're managing. – pondersome Jul 31 '18 at 03:37

1 Answers1

0

@Scott Craner's code is fine but it takes times to get familiar with the logic how Excel manages ranges. I suggest an approach to split Scott's highly advanced expression into smaller pieces that make a single operation, and analyze what happens:

Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
Dim r7 As Range, r8 As Range

Set r1 = tws.Cells(tws.Rows.Count, dataClmStrt + 2)
Debug.Print r1.Row, r1.Rows.Count, r1.Column, r1.Columns.Count
Set r2 = r1.End(xlUp)
Debug.Print r2.Row, r2.Rows.Count, r2.Column, r2.Columns.Count
Set r3 = r2.Offset(1)
Debug.Print r3.Row, r3.Rows.Count, r3.Column, r3.Columns.Count
Set r4 = r3.Resize(38, 1)
Debug.Print r4.Row, r4.Rows.Count, r4.Column, r4.Columns.Count
Set r7 = rng.Offset(, dataClmStrt)
Debug.Print r7.Row, r7.Rows.Count, r7.Column, r7.Columns.Count
Set r8 = r7.Resize(, 38)
Debug.Print r8.Row, r8.Rows.Count, r8.Column, r8.Columns.Count
r4.Value = Application.Transpose(r8)
AcsErno
  • 1,597
  • 1
  • 7
  • 10