title: Build hierarchy type presentation of data in Excel
body: <p>I try to convert my excel data to tree data using vba.</p>
  
  <pre><code>Sub MakeTree()
  
      Dim r As Integer
      ' Iterate through the range, looking for the Root
      For r = 1 To Range("Data").Rows.Count
          If Range("Data").Cells(r, 1) = "Root" Then
              DrawNode Range("Data").Cells(r, 2), 0, 0
          End If
      Next
  
  End Sub
  
  Sub DrawNode(ByRef header As String, ByRef row As Integer, ByRef depth As Integer)
  'The DrawNode routine draws the current node, and all child nodes.
  ' First we draw the header text:
      Cells(Range("Destination").row + row, Range("Destination").Column + depth) = header
  
      Dim r As Integer
      'Then loop through, looking for instances of that text
      For r = 1 To Range("Data").Rows.Count
          If Range("Data").Cells(r, 1) = header Then
          'Bang!  We've found one!  Then call itself to see if there are any child nodes
              row = row + 1
              DrawNode Range("Data").Cells(r, 2), row, depth + 1
          End If
      Next
  End Sub
  </code></pre>
  
  <p>My excel data like this,</p>
  
  <p><a href="https://i.stack.imgur.com/pMjwL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pMjwL.png" alt="Source data"></a></p>
  
  <p>I try to convert tree data like this by using my vba code.</p>
  
  <p><a href="https://i.stack.imgur.com/NO1zW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NO1zW.png" alt="Output data format"></a></p>
  
  <p>But above code didn't work for me.</p>
  
  <p>Anyone suggest me ?</p>
  
  <p>Thanks</p>