Special Offer: Buy 1 course, get 2nd free! Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!
Sub Normalize() Dim SourceRange As Range Dim SourceArr As Variant Dim r As Long, c As Long Dim DestR As Long ' Update as needed Const StartDate As Date = #6/1/2014# ' Update as needed Set SourceRange = ActiveSheet.Range("a1:ak13") SourceArr = SourceRange.Value Worksheets.Add Range("a1:f1").Value = Array("Die", "Machine", "Part Desc", "Part No", "Date", "Units") DestR = 2 For r = 4 To UBound(SourceArr, 1) For c = 7 To UBound(SourceArr, 2) If SourceArr(r, c) <> "" Then Cells(DestR, 1) = SourceArr(r, 1) Cells(DestR, 2) = SourceArr(r, 2) Cells(DestR, 3) = SourceArr(r, 3) Cells(DestR, 4) = SourceArr(r, 4) Cells(DestR, 5) = StartDate + SourceArr(1, c) - 1 Cells(DestR, 6) = SourceArr(r, c) DestR = DestR + 1 End If Next Next MsgBox "Done" End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Excel VBA Workbook - Change hours||2||41|
|Office applications crashing||7||34|
|Microsoft Excel - Select All Computers and Specific Applicaiton||2||26|
|How to set correct rule in Outlook 2013/2016||1||23|
Join the community of 500,000 technology professionals and ask your questions.