Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Sub Flatten() Dim LastR As Long, LastC As Long Dim arr As Variant Dim RCounter As Long, CCounter As Long Dim DestArr() As Variant Dim DestRow As Long Dim wb As Workbook With ThisWorkbook.Worksheets("Sheet4") 'rename as needed LastR = .Cells(.Rows.Count, "b").End(xlUp).Row LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column arr = .Range(.Cells(1, "b"), .Cells(LastR, LastC)).Value End With ReDim DestArr(1 To (LastR - 1) * (LastC - 2), 1 To 3) As Variant For CCounter = 2 To (LastC - 1) For RCounter = 2 To LastR DestRow = DestRow + 1 DestArr(DestRow, 1) = arr(RCounter, 1) DestArr(DestRow, 2) = arr(1, CCounter) DestArr(DestRow, 3) = arr(RCounter, CCounter) Next Next Set wb = Workbooks.Add [a1:c1] = Array("Module", "User", "Permission") [a2].Resize(UBound(DestArr, 1), UBound(DestArr, 2)).Value = DestArr wb.SaveAs "c:\Test\Foo.xls", xlExcel12 wb.Close End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.