transpose VBA

S

shahsavand

Board Regular
Joined
Dec 8, 2014
Messages
74
I would like to transpose columns to rows

Please note that the spreadsheet is quite large.
Current file layout example:


<colgroup><col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:5668;width:116pt" width="155"> <col style="mso-width-source:userset;mso-width-alt:3035; width:62pt" width="83" span="12"> </colgroup><tbody>
[TD="class: xl70, width: 83"]Version[/TD]
[TD="class: xl70, width: 155"]Product[/TD]
[TD="class: xl70, width: 83"]9601[/TD]
[TD="class: xl70, width: 83"]9602[/TD]
[TD="class: xl70, width: 83"]9603[/TD]
[TD="class: xl70, width: 83"]9604[/TD]
[TD="class: xl70, width: 83"]9605[/TD]
[TD="class: xl70, width: 83"]9606[/TD]
[TD="class: xl70, width: 83"]9607[/TD]
[TD="class: xl70, width: 83"]9608[/TD]
[TD="class: xl70, width: 83"]9609[/TD]
[TD="class: xl70, width: 83"]9610[/TD]
[TD="class: xl70, width: 83"]9611[/TD]
[TD="class: xl70, width: 83"]9612[/TD]

[TD="class: xl67"]Budget[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl68"]2600[/TD]
[TD="class: xl68"]5800[/TD]
[TD="class: xl68"]5800[/TD]
[TD="class: xl68"]5800[/TD]
[TD="class: xl68"]5800[/TD]
[TD="class: xl68"]5800[/TD]
[TD="class: xl68"]5800[/TD]
[TD="class: xl68"]5800[/TD]
[TD="class: xl68"]5800[/TD]
[TD="class: xl68"]6200[/TD]
[TD="class: xl68"]6200[/TD]
[TD="class: xl68"]7400[/TD]

[TD="class: xl67"]Sales Loss[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl71"]0%[/TD]
[TD="class: xl71"]44%[/TD]
[TD="class: xl71"]79%[/TD]
[TD="class: xl71"]55%[/TD]
[TD="class: xl71"]20%[/TD]
[TD="class: xl71"]10%[/TD]
[TD="class: xl71"]26%[/TD]
[TD="class: xl71"]7%[/TD]
[TD="class: xl71"]0%[/TD]
[TD="class: xl71"]62%[/TD]
[TD="class: xl71"]0%[/TD]
[TD="class: xl71"]58%[/TD]

[TD="class: xl67"]Stock[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl69"]12926[/TD]
[TD="class: xl69"]7862[/TD]
[TD="class: xl69"]2942[/TD]
[TD="class: xl69"]6258[/TD]
[TD="class: xl69"]11102[/TD]
[TD="class: xl69"]12569[/TD]
[TD="class: xl69"]10335[/TD]
[TD="class: xl69"]12944[/TD]
[TD="class: xl69"]15254[/TD]
[TD="class: xl69"]5702[/TD]
[TD="class: xl69"]15245[/TD]
[TD="class: xl69"]7379[/TD]

[TD="class: xl67"]Actual[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl68"]3920[/TD]
[TD="class: xl68"]4230[/TD]
[TD="class: xl68"]6967[/TD]
[TD="class: xl68"]5941[/TD]
[TD="class: xl68"]4820[/TD]
[TD="class: xl68"]6291[/TD]
[TD="class: xl68"]5063[/TD]
[TD="class: xl68"]6394[/TD]
[TD="class: xl68"]7410[/TD]
[TD="class: xl68"]3401[/TD]
[TD="class: xl68"]8166[/TD]
[TD="class: xl68"]9611[/TD]

[TD="class: xl67"]MAX[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl68"]6240[/TD]
[TD="class: xl68"]13920[/TD]
[TD="class: xl68"]13920[/TD]
[TD="class: xl68"]13920[/TD]
[TD="class: xl68"]13920[/TD]
[TD="class: xl68"]13920[/TD]
[TD="class: xl68"]13920[/TD]
[TD="class: xl68"]13920[/TD]
[TD="class: xl68"]13920[/TD]
[TD="class: xl68"]14880[/TD]
[TD="class: xl68"]14880[/TD]
[TD="class: xl68"]17760[/TD]

[TD="class: xl67"]Budget[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl68"]9400[/TD]
[TD="class: xl68"]14800[/TD]
[TD="class: xl68"]15500[/TD]
[TD="class: xl68"]16100[/TD]
[TD="class: xl68"]16700[/TD]
[TD="class: xl68"]17400[/TD]
[TD="class: xl68"]18000[/TD]
[TD="class: xl68"]16800[/TD]
[TD="class: xl68"]15600[/TD]
[TD="class: xl68"]14400[/TD]
[TD="class: xl68"]13200[/TD]
[TD="class: xl68"]12000[/TD]

[TD="class: xl67"]Sales Loss[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]1[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]1[/TD]
[TD="class: xl68"]1[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]0[/TD]

[TD="class: xl67"]Stock[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl68"]58008[/TD]
[TD="class: xl68"]48216[/TD]
[TD="class: xl68"]27480[/TD]
[TD="class: xl68"]16932[/TD]
[TD="class: xl68"]28772[/TD]
[TD="class: xl68"]13584[/TD]
[TD="class: xl68"]1080[/TD]
[TD="class: xl68"]27232[/TD]
[TD="class: xl68"]36724[/TD]
[TD="class: xl68"]24916[/TD]
[TD="class: xl68"]39380[/TD]
[TD="class: xl68"]26564[/TD]

[TD="class: xl67"]Actual[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl68"]15360[/TD]
[TD="class: xl68"]22008[/TD]
[TD="class: xl68"]18728[/TD]
[TD="class: xl68"]17484[/TD]
[TD="class: xl68"]19664[/TD]
[TD="class: xl68"]16092[/TD]
[TD="class: xl68"]17204[/TD]
[TD="class: xl68"]9824[/TD]
[TD="class: xl68"]10672[/TD]
[TD="class: xl68"]10944[/TD]
[TD="class: xl68"]13976[/TD]
[TD="class: xl68"]11896[/TD]

[TD="class: xl67"]MAX[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl68"]22560[/TD]
[TD="class: xl68"]35520[/TD]
[TD="class: xl68"]37200[/TD]
[TD="class: xl68"]38640[/TD]
[TD="class: xl68"]40080[/TD]
[TD="class: xl68"]41760[/TD]
[TD="class: xl68"]43200[/TD]
[TD="class: xl68"]40320[/TD]
[TD="class: xl68"]37440[/TD]
[TD="class: xl68"]34560[/TD]
[TD="class: xl68"]31680[/TD]
[TD="class: xl68"]28800

[/TD]

</tbody>

Result:

<colgroup><col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="width:48pt" width="64" span="4"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> </colgroup><tbody>
[TD="class: xl68, width: 123"]Month[/TD]
[TD="class: xl69, width: 123"]Products[/TD]
[TD="class: xl70, width: 64"]Budget[/TD]
[TD="class: xl70, width: 64"]Actual[/TD]
[TD="class: xl70, width: 64"]MAX[/TD]
[TD="class: xl70, width: 64"]Stock[/TD]
[TD="class: xl70, width: 81"]Sales Loss[/TD]

[TD="class: xl68, width: 123"]9601[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]2600[/TD]
[TD="class: xl72"]3920[/TD]
[TD="class: xl72"]6240[/TD]
[TD="class: xl73"]12926[/TD]
[TD="class: xl67"]0%[/TD]

[TD="class: xl68, width: 123"]9602[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]5800[/TD]
[TD="class: xl72"]4230[/TD]
[TD="class: xl72"]13920[/TD]
[TD="class: xl73"]7862[/TD]
[TD="class: xl67"]44%[/TD]

[TD="class: xl68, width: 123"]9603[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]5800[/TD]
[TD="class: xl72"]6967[/TD]
[TD="class: xl72"]13920[/TD]
[TD="class: xl73"]2942[/TD]
[TD="class: xl67"]79%[/TD]

[TD="class: xl68, width: 123"]9604[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]5800[/TD]
[TD="class: xl72"]5941[/TD]
[TD="class: xl72"]13920[/TD]
[TD="class: xl73"]6258[/TD]
[TD="class: xl67"]55%[/TD]

[TD="class: xl68, width: 123"]9605[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]5800[/TD]
[TD="class: xl72"]4820[/TD]
[TD="class: xl72"]13920[/TD]
[TD="class: xl73"]11102[/TD]
[TD="class: xl67"]20%[/TD]

[TD="class: xl68, width: 123"]9606[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]5800[/TD]
[TD="class: xl72"]6291[/TD]
[TD="class: xl72"]13920[/TD]
[TD="class: xl73"]12569[/TD]
[TD="class: xl67"]10%[/TD]

[TD="class: xl68, width: 123"]9607[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]5800[/TD]
[TD="class: xl72"]5063[/TD]
[TD="class: xl72"]13920[/TD]
[TD="class: xl73"]10335[/TD]
[TD="class: xl67"]26%[/TD]

[TD="class: xl68, width: 123"]9608[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]5800[/TD]
[TD="class: xl72"]6394[/TD]
[TD="class: xl72"]13920[/TD]
[TD="class: xl73"]12944[/TD]
[TD="class: xl67"]7%[/TD]

[TD="class: xl68, width: 123"]9609[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]5800[/TD]
[TD="class: xl72"]7410[/TD]
[TD="class: xl72"]13920[/TD]
[TD="class: xl73"]15254[/TD]
[TD="class: xl67"]0%[/TD]

[TD="class: xl68, width: 123"]9610[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]6200[/TD]
[TD="class: xl72"]3401[/TD]
[TD="class: xl72"]14880[/TD]
[TD="class: xl73"]5702[/TD]
[TD="class: xl67"]62%[/TD]

[TD="class: xl68, width: 123"]9611[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]6200[/TD]
[TD="class: xl72"]8166[/TD]
[TD="class: xl72"]14880[/TD]
[TD="class: xl73"]15245[/TD]
[TD="class: xl67"]0%[/TD]

[TD="class: xl68, width: 123"]9612[/TD]
[TD="class: xl71"]A[/TD]
[TD="class: xl72"]7400[/TD]
[TD="class: xl72"]9611[/TD]
[TD="class: xl72"]17760[/TD]
[TD="class: xl73"]7379[/TD]
[TD="class: xl67"]58%[/TD]

[TD="class: xl68, width: 123"]9601[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]9400[/TD]
[TD="class: xl75"]15360[/TD]
[TD="class: xl75"]22560[/TD]
[TD="class: xl75"]58008[/TD]
[TD="class: xl76"]0%[/TD]

[TD="class: xl68, width: 123"]9602[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]14800[/TD]
[TD="class: xl75"]22008[/TD]
[TD="class: xl75"]35520[/TD]
[TD="class: xl75"]48216[/TD]
[TD="class: xl76"]0%[/TD]

[TD="class: xl68, width: 123"]9603[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]15500[/TD]
[TD="class: xl75"]18728[/TD]
[TD="class: xl75"]37200[/TD]
[TD="class: xl75"]27480[/TD]
[TD="class: xl76"]26%[/TD]

[TD="class: xl68, width: 123"]9604[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]16100[/TD]
[TD="class: xl75"]17484[/TD]
[TD="class: xl75"]38640[/TD]
[TD="class: xl75"]16932[/TD]
[TD="class: xl76"]56%[/TD]

[TD="class: xl68, width: 123"]9605[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]16700[/TD]
[TD="class: xl75"]19664[/TD]
[TD="class: xl75"]40080[/TD]
[TD="class: xl75"]28772[/TD]
[TD="class: xl76"]28%[/TD]

[TD="class: xl68, width: 123"]9606[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]17400[/TD]
[TD="class: xl75"]16092[/TD]
[TD="class: xl75"]41760[/TD]
[TD="class: xl75"]13584[/TD]
[TD="class: xl76"]67%[/TD]

[TD="class: xl68, width: 123"]9607[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]18000[/TD]
[TD="class: xl75"]17204[/TD]
[TD="class: xl75"]43200[/TD]
[TD="class: xl75"]1080[/TD]
[TD="class: xl76"]98%[/TD]

[TD="class: xl68, width: 123"]9608[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]16800[/TD]
[TD="class: xl75"]9824[/TD]
[TD="class: xl75"]40320[/TD]
[TD="class: xl75"]27232[/TD]
[TD="class: xl76"]32%[/TD]

[TD="class: xl68, width: 123"]9609[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]15600[/TD]
[TD="class: xl75"]10672[/TD]
[TD="class: xl75"]37440[/TD]
[TD="class: xl75"]36724[/TD]
[TD="class: xl76"]2%[/TD]

[TD="class: xl68, width: 123"]9610[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]14400[/TD]
[TD="class: xl75"]10944[/TD]
[TD="class: xl75"]34560[/TD]
[TD="class: xl75"]24916[/TD]
[TD="class: xl76"]28%[/TD]

[TD="class: xl68, width: 123"]9611[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]13200[/TD]
[TD="class: xl75"]13976[/TD]
[TD="class: xl75"]31680[/TD]
[TD="class: xl75"]39380[/TD]
[TD="class: xl76"]0%[/TD]

[TD="class: xl68, width: 123"]9612[/TD]
[TD="class: xl74"]B[/TD]
[TD="class: xl75"]12000[/TD]
[TD="class: xl75"]11896[/TD]
[TD="class: xl75"]28800[/TD]
[TD="class: xl75"]26564[/TD]
[TD="class: xl76"]8%[/TD]

</tbody>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. From the menu, click Insert > Module. Paste the following code into the window that opens:

Rich (BB code): 
Sub Rearrange()
Dim sh1 As Worksheet, sh2 As Worksheet, Dict As Object, ctr As Long, MyData As Variant
Dim c As Long, r As Long, rr As Long, op As Variant, res(1 To 50000, 1 To 1) As String

    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.Add 1, "Month|Products|Budget|Actual|MAX|Stock|Sales Loss"
    ctr = 1
    MyData = sh1.Range(sh1.Range("A1"), sh1.Cells(sh1.Cells(Rows.Count, 1).End(xlUp).Row, sh1.Cells(1, Columns.Count).End(xlToLeft).Column)).Value
    
    For c = 3 To UBound(MyData, 2)
        For r = 2 To UBound(MyData) Step 5
            ctr = ctr + 1
            Dict.Add ctr, MyData(1, c) & "|" & MyData(r, 2) & "|" & MyData(r, c) & "|" & MyData(r + 3, c) & _
                                      "|" & MyData(r + 4, c) & "|" & MyData(r + 2, c) & "|" & MyData(r + 1, c)
        Next r
    Next c
    
    sh2.Cells.ClearContents
    op = Dict.items
    ctr = 0
    rr = 1
    For r = 0 To UBound(op)
        ctr = ctr + 1
        res(ctr, 1) = op(r)
        If ctr = 50000 Then
            sh2.Cells(rr, 1).Resize(50000).Value = res
            ctr = 0
            rr = rr + 50000
            Erase res
        End If
    Next r
    sh2.Cells(rr, 1).Resize(50000).Value = res
    
    sh2.Columns("A:A").TextToColumns Destination:=sh2.Range("A1"), DataType:=xlDelimited, _
         Other:=True, OtherChar:="|"
    
    With sh2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=sh2.Range("B2")
        .SortFields.Add Key:=sh2.Range("A2")
        .SetRange sh2.Range("A:G")
        .Header = xlYes
        .Apply
    End With
    Selection.Style = "Percent"
    
End Sub
Sheet1 is where you have your input table, Sheet2 is where you want your output table. Both sheets must exist. The input table is assumed to start in A1, the output table will start in A1. Close the VBA editor with Alt-Q. In Excel, press Alt-F8, choose Rearrange and click Run.

Let us know if this works for you.
 
Upvote 0
Eric W said:
Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. From the menu, click Insert > Module. Paste the following code into the window that opens:

Rich (BB code): 
Sub Rearrange()
Dim sh1 As Worksheet, sh2 As Worksheet, Dict As Object, ctr As Long, MyData As Variant
Dim c As Long, r As Long, rr As Long, op As Variant, res(1 To 50000, 1 To 1) As String

    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.Add 1, "Month|Products|Budget|Actual|MAX|Stock|Sales Loss"
    ctr = 1
    MyData = sh1.Range(sh1.Range("A1"), sh1.Cells(sh1.Cells(Rows.Count, 1).End(xlUp).Row, sh1.Cells(1, Columns.Count).End(xlToLeft).Column)).Value
    
    For c = 3 To UBound(MyData, 2)
        For r = 2 To UBound(MyData) Step 5
            ctr = ctr + 1
            Dict.Add ctr, MyData(1, c) & "|" & MyData(r, 2) & "|" & MyData(r, c) & "|" & MyData(r + 3, c) & _
                                      "|" & MyData(r + 4, c) & "|" & MyData(r + 2, c) & "|" & MyData(r + 1, c)
        Next r
    Next c
    
    sh2.Cells.ClearContents
    op = Dict.items
    ctr = 0
    rr = 1
    For r = 0 To UBound(op)
        ctr = ctr + 1
        res(ctr, 1) = op(r)
        If ctr = 50000 Then
            sh2.Cells(rr, 1).Resize(50000).Value = res
            ctr = 0
            rr = rr + 50000
            Erase res
        End If
    Next r
    sh2.Cells(rr, 1).Resize(50000).Value = res
    
    sh2.Columns("A:A").TextToColumns Destination:=sh2.Range("A1"), DataType:=xlDelimited, _
         Other:=True, OtherChar:="|"
    
    With sh2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=sh2.Range("B2")
        .SortFields.Add Key:=sh2.Range("A2")
        .SetRange sh2.Range("A:G")
        .Header = xlYes
        .Apply
    End With
    Selection.Style = "Percent"
    
End Sub
Sheet1 is where you have your input table, Sheet2 is where you want your output table. Both sheets must exist. The input table is assumed to start in A1, the output table will start in A1. Close the VBA editor with Alt-Q. In Excel, press Alt-F8, choose Rearrange and click Run.

Let us know if this works for you.
Click to expand...

you are awesome. yeas its working
 
Upvote 0
Eric W said:
Rich (BB code): 
Sub Rearrange()
Dim sh1 As Worksheet, sh2 As Worksheet, Dict As Object, ctr As Long, MyData As Variant
Dim c As Long, r As Long, rr As Long, op As Variant, res(1 To 50000, 1 To 1) As String

    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.Add 1, "Month|Products|Budget|Actual|MAX|Stock|Sales Loss"
    ctr = 1
    MyData = sh1.Range(sh1.Range("A1"), sh1.Cells(sh1.Cells(Rows.Count, 1).End(xlUp).Row, sh1.Cells(1, Columns.Count).End(xlToLeft).Column)).Value
    
    For c = 3 To UBound(MyData, 2)
        For r = 2 To UBound(MyData) Step 5
            ctr = ctr + 1
            Dict.Add ctr, MyData(1, c) & "|" & MyData(r, 2) & "|" & MyData(r, c) & "|" & MyData(r + 3, c) & _
                                      "|" & MyData(r + 4, c) & "|" & MyData(r + 2, c) & "|" & MyData(r + 1, c)
        Next r
    Next c
    
    sh2.Cells.ClearContents
    op = Dict.items
    ctr = 0
    rr = 1
    For r = 0 To UBound(op)
        ctr = ctr + 1
        res(ctr, 1) = op(r)
        If ctr = 50000 Then
            sh2.Cells(rr, 1).Resize(50000).Value = res
            ctr = 0
            rr = rr + 50000
            Erase res
        End If
    Next r
    sh2.Cells(rr, 1).Resize(50000).Value = res
    
    sh2.Columns("A:A").TextToColumns Destination:=sh2.Range("A1"), DataType:=xlDelimited, _
         Other:=True, OtherChar:="|"
    
    With sh2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=sh2.Range("B2")
        .SortFields.Add Key:=sh2.Range("A2")
        .SetRange sh2.Range("A:G")
        .Header = xlYes
        .Apply
    End With
    Selection.Style = "Percent"
    
End Sub
Click to expand...
Eric, sooo many lines of code. :diablo:
A little bit shorter...
Code: 
[table="width: 500"]
[tr]
	[td]Sub RearrangeData()
  Dim R As Long, LC As Long, WS1 As Worksheet, WS2 As Worksheet
  Set WS1 = Sheets("Sheet1")
  Set WS2 = Sheets("Sheet2")
  LC = WS1.Cells(1, Columns.Count).End(xlToLeft).Column
  WS2.Range("A1:G1") = Array("Month", "Products", "Budget", "Actual", "MAX", "Stock", "Sales Loss")
  For R = 2 To WS1.Cells(Rows.Count, "A").End(xlUp).Row Step 5
    With WS2.Cells(Rows.Count, "A").End(xlUp)
      .Offset(1, 2).Resize(LC - 2, 5) = Application.Transpose(Application.Index(WS1.Cells, Evaluate("{" & R & ";" & R + 3 & ";" & R + 4 & ";" & R + 2 & ";" & R + 1 & "}"), Evaluate("COLUMN(C:N)")))
      .Offset(1, 6).Resize(LC - 2).NumberFormat = "0%"
      .Offset(1).Resize(LC - 2) = Application.Transpose(WS1.Range("C1").Resize(, LC - 2))
      .Offset(1, 1).Resize(LC - 2) = WS1.Cells(R, "B").Value
    End With
  Next
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Rick Rothstein said:
Eric, sooo many lines of code. :diablo:
A little bit shorter...
Click to expand...

:pray:

I know no one better at writing concise code and using the built-in functionality of Excel and VBA to its best advantage. I think I need to sell all my electronic gadgets and join a vegan commune somewhere . . .
 
Last edited:
Upvote 0
Eric W said:
:pray:

I know no one better at writing concise code and using the built-in functionality of Excel and VBA to its best advantage. I think I need to sell all my electronic gadgets and join a vegan commune somewhere . . .
Click to expand...

Wow! Thank you for those kind words. Every now and then I get "lucky" and an idea for a different, sometimes concise method of attack for a problem occurs to me... that is all that happened this time. And please, don't sell your computer and run off to a commune... the MrExcel Forum benefits greatly from your inputs, trust me, it does.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA script to transpose 2 columns
Replies
3
Views
859
John Davis
J
V
  • Question Question
using VBA instead of fomula in the sheet
Replies
0
Views
543
vishal120
V

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top