SHARE
TWEET

Untitled

a guest Jun 25th, 2019 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub reArrange()
  2.  
  3.     Dim inFirstRng As Range
  4.     Dim inRng As Range
  5.     Dim inCur As Variant
  6.     Dim outFirstRng As Range
  7.     Dim outCurRng As Range
  8.     Dim ws As Worksheet
  9.  
  10.     'CHANGE ARGUMENT TO YOUR SHEET NAME
  11.     Set ws = Worksheets("Sheet2")
  12.  
  13.     With ws
  14.         'CHANGE ARGUMENT TO WHATEVER THE FIRST CELL OR YOUR DATA INPUT IS IN COLUMN A
  15.         Set inFirstRng = .Range("A3")
  16.         Set inRng = .Range(inFirstRng, inFirstRng.End(xlDown))
  17.         'CHANGE ARGUMENT TO WHATEVER THE FIRST CELL OR YOUR DATA OUTPUT IS IN COLUMN A
  18.         Set outFirstRng = .Range("A9")
  19.         Set outCurRng = outFirstRng
  20.     End With
  21.  
  22.     For Each cell In inRng.Cells
  23.  
  24.         inCur = WorksheetFunction.Transpose(Split(cell.Value, "^"))
  25.         outCurRng.Resize(UBound(inCur), 1).Value = inCur
  26.  
  27.         With ws
  28.             .Range("G" & outCurRng.Row & ":L" & outCurRng.Row).Value = _
  29.             .Range("G" & cell.Row & ":L" & cell.Row).Value
  30.         End With
  31.  
  32.         Set outCurRng = outCurRng.Offset(UBound(inCur), 0)
  33.  
  34.     Next cell
  35.  
  36.     ws.Range("F" & outFirstRng.Row & ":F" & outCurRng.Row - 1).Value = 1
  37.  
  38. End Sub
  39.      
  40. Sub Breakout()
  41.     Application.ScreenUpdating = False
  42.     LR = Cells(Rows.Count, 1).End(xlUp).Row
  43.     For r = LR To 2 Step -1
  44.         Set MyCell = Cells(r, 1)
  45.         Arry = Split(MyCell.Value, "^")
  46.         For c = 0 To UBound(Arry)
  47.             If c > 0 Then MyCell.Offset(c, 0).EntireRow.Insert
  48.             MyCell.Offset(c, 0) = Arry(c)
  49.         Next c
  50.     Next r
  51. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top