Columns to rows

[TroyB]

Hello all,

I am hoping somebody can help me fix and speed up the VBA below.

My intent is to make Columns "T" to "Last Column with data" become rows. and the VBA i have below is just not working very good and its very slow


Current:

Sub Columns_2_rows()



Dim rownum As Long

Dim rownum2 As Long

Dim colnum As Long

Dim wb As Workbook

Dim ws As Worksheet



Application.ScreenUpdating = False

Application.DisplayAlerts = False

  

Set ws = ThisWorkbook.Sheets.Add(After:= _

ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

ws.Name = "Result"

Sheets("Raw_data").Select


Set wb = ActiveWorkbook

rownum = 2

rownum2 = 2



Sheets("Raw_data").Range("A1:S1").Copy Sheets("Result").Range("A1")

Sheets("Result").Range("T1").Value = "Date"

Sheets("Result").Range("U1").Value = "Value"



Do Until Sheets("Forecast Data").Cells(rownum, 1).Value = ""

colnum = 43

    Do Until colnum = 67

    Sheets("Raw_data").Range(Cells(rownum, 1), Cells(rownum, 19)).Copy Sheets("Result").Cells(rownum2, 1)

    Sheets("Forecast Data").Cells(1, colnum).Copy Sheets("Result").Cells(rownum2, 20)

    myval = Sheets("Raw_data").Cells(rownum, colnum).Value

        If myval <> "0" Then

        myval = myval / 1000

        Else

        myval = 0

        End If

    Sheets("Result").Cells(rownum2, 21) = myval

    rownum2 = rownum2 + 1

    colnum = colnum + 1

    Loop

rownum = rownum + 1

Loop



Application.DisplayAlerts = True

Application.ScreenUpdating = True

  

End Sub

 

[trunten]

If you are willing to provide a quick preview (screen shots would do) of how the data looks initially and what you would need the output to be I could look into this for you.

 

[TroyB]

Hello Trunten,

Thank you very much for the reply

The original Data would look like this:

Heading 1

Heading 2

Customer #

State

Country

Code

Cust Group

Customer Name

Item#

Heading 10

Unit Cost

Heading 12

Heading 13

Heading 14

Heading 15

Heading 16

Heading 17

Heading 18

Heading 19

10/1/2019​

10/2/2019​

11/19/2019​

11/19/2019​

12/19/2019​

12/19/2019​

1/1/2020​

1/1/2020​

2/1/2020​

2/1/2020​

3/1/2020​

3/1/2020​

4/1/2020​

4/1/2020​

5/1/2020​

5/1/2020​

6/1/2020​

6/1/2020​

7/1/2020​

7/1/2020​

8/1/2020​

8/1/2020​

9/1/2020​

9/1/2020​

########​

########​

########​

########​

########​

########​

1/1/2021​

1/1/2021​

2/1/2021​

2/1/2021​

3/1/2021​

3/1/2021​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

170​

31.71641791​

170​

31.71641791​

170​

31.71641791​

170​

31.71641791​

170​

31.71642​

170​

31.71642​

170​

31.71642​

170​

31.71642​

170​

31.71642​

170​

31.71642​

170​

31.71642​

170​

31.71642​

170​

0​

170​

31.71642​

170​

31.71642​

170​

31.71642​

170​

31.71642​

170​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

86.1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

0​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

86.1​

1​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#2 REV 3

Z293

0​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

1050.9​

Customer rep 2

Sales Rep 2

C007978

CA

USA

EP

Group 3

Customer 2

Item#2 REV 3

Z263

34.89​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Customer rep 1

Sales Rep 1

C007978

IL

USA

AT

Group 3

Customer 2

Item#2 REV 3

Z293

1.26​

50​

39.68253968​

50​

39.68253968​

50​

39.68253968​

50​

39.68253968​

50​

39.68254​

50​

39.68254​

50​

39.68254​

50​

39.68254​

50​

39.68254​

50​

39.68254​

50​

39.68254​

50​

39.68254​

50​

0​

50​

39.68254​

50​

39.68254​

50​

39.68254​

50​

39.68254​

50​

39.68254​

Customer rep 1

Sales Rep 1

C007978

CA

USA

EP

Group 3

Customer 2

Item#2 REV 3

Z255

3.68​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

7360​

2000​

Customer rep 1

Sales Rep 1

C007978

CA

USA

EP

Group 3

Customer 2

Item#1 REV B

Z255

4.07​

0​

0​

0​

0​

8140​

2000​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

12210​

3000​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Customer rep 1

Sales Rep 1

C007978

CA

USA

EP

Group 3

Customer 2

Item#5 REV B

Z255

3.71​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

1855​

500​

After running the VBA it would look like this:

Heading 1

Heading 2

Customer #

State

Country

Code

Cust Group

Customer Name

Item#

Heading 10

Unit Cost

Heading 12

Heading 13

Heading 14

Heading 15

Heading 16

Heading 17

Heading 18

Heading 19

Value

Qty

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

10/1/2019​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

10/2/2019​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

11/19/2019​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

11/19/2019​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

12/19/2019​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

12/19/2019​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

1/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

1/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

2/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

2/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

3/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

3/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

4/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

4/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

5/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

5/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

6/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

6/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

7/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

7/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

8/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

8/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

9/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

9/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

10/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

10/1/2020​

0​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

11/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

11/1/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

12/1/2020​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

12/2/2020​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

1/1/2021​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

1/1/2021​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

2/1/2021​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

2/1/2021​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

3/1/2021​

1000​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

61.51​

3/1/2021​

16.25752​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

10/1/2019​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

10/2/2019​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

11/19/2019​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

11/19/2019​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

12/19/2019​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

12/19/2019​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

1/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

1/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

2/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

2/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

3/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

3/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

4/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

4/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

5/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

5/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

6/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

6/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

7/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

7/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

8/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

8/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

9/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

9/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

10/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

10/1/2020​

0​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

11/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

11/1/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

12/1/2020​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

12/2/2020​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

1/1/2021​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

1/1/2021​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

2/1/2021​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

2/1/2021​

31.71642​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

3/1/2021​

170​

Customer rep 1

Sales Rep 1

C023783

MN

USA

AT

Group 1

Customer 1

Item#1 REV B

Z293

5.36​

3/1/2021​

31.71642​

 

[TroyB]

Can anybody help with this?

 

[sandy666]

with Power Query try Unpivot Only Selected (from T to the end) Columns

 

[DanteAmor]

I put a macro, it should be faster.

Change "Raw_data" and "Result" for the names of your sheets.

Sub columns_to_rows()
  Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, lc As Long, n As Long
  Dim a As Variant, b As Variant, m As Long, i As Long, j As Long, k As Long
  '
  Set sh1 = Sheets("Raw_data")
  Set sh2 = Sheets("Result")
  '
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  sh2.Rows("2:" & Rows.Count).ClearContents
  a = sh1.Range("A1", sh1.Cells(lr, lc)).Value
  n = (lr - 1) * (lc - 19)
  ReDim b(1 To n, 1 To 21)
  m = 1
  '
  For i = 2 To UBound(a)
    For j = 20 To lc
      For k = 1 To 19
        b(m, k) = a(i, k)
      Next
      b(m, 20) = a(1, j)
      b(m, 21) = a(i, j)
      m = m + 1
    Next
  Next
  sh2.Range("A2").Resize(UBound(b), 21).Value = b
End Sub

 

[TroyB]

Dante, Kudo's to you!!! thank you very very much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.