title: VBA Add row in table if new combination appears - partially solved
body: <p>I am a bit struggling with a way to variably adding lines to table according to capex category, partner number and period.</p>
  <p>Mu final table looks like (more sections, but for example), it is not a listobject item:
  <a href="https://i.stack.imgur.com/7wWTb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7wWTb.png" alt="final table" /></a></p>
  <p>my source looks like (more pivot tables, but for example):
  <a href="https://i.stack.imgur.com/IyFkv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IyFkv.png" alt="source data" /></a></p>
  <p>I am indexing data from source to final table. But when combination do not exist, like the orange field in data source, in final table it should be Capex O - 3to5 years, and partner 1115, I don't know how to add it.</p>
  <p>I thought of find function in case I would create unique ID for every line (combination of Capex letter, position (period ID) and partner), but in this case I am a bit stuck with different treatment of periods in final table and source. In final table 1-2 years and 2-3 years are combined as well as 3-4 years and 4-5 years. And I don't think transposing the source will be useful (you can prove me wrong).</p>
  <p>Also I would need add lines in final table only for situations where value is &lt;&gt; 0.</p>
  <p>I would need to add the new row in A-Z order for every capex category and period (except if its 999999, which should always be in first position; and 999999 is default value, so it is already in every capex category and period)</p>
  <p>Every green column has its own calculated workbook where source is listed on separate sheet (Pivot)</p>
  <p>Hope I described it clear, in any doubts, please let me know.</p>
  <p><strong>EDIT 18 Mar 22:</strong></p>
  <p>So, I almost figured it out, except couple issues.</p>
  <p>First I created support columns in pivot sheet using concatenate on partner ID and position (there is such concat in original table, not presented before)</p>
  <p>My code (wsSUM is declared in Public section of the code, as it is used accross modules; also you don't see whole code, only part regarding this issue):</p>
  <pre><code>Dim wbname As String
  Dim wsp As Worksheet, wspsqa As Worksheet
  Dim lcol As Long, lrow As Long
  Dim i As Integer, n As Integer, x As Integer, r As Integer, RowCount As Integer, FR As Integer, LR As Integer, z As Integer, t As Integer
  Dim catID As Variant
  Dim sInt As Range
  
  
  
          Rows(&quot;3:3&quot;).Find(What:=&quot;Total to IKOS&quot;).Select
          Range(Selection, Selection.End(xlDown)).Select
      i = Selection.Rows.Count
  .
  .
  .
  
   z = 0 'row number in pivot - wsp
      n = 0 'column number in pivot - wsp
      t = 0 'row number of left,9 for catID - wsSUM
      'i is declared above, rows count in wsSUM for current quarter
  
      For z = 2 To lrow
          For n = 5 To 8
              If wsp.Cells(z, lcol + n).Value &lt;&gt; &quot;&quot; Then
              catID = wsp.Cells(z, lcol + n).Value
                  'sInt = CInt(wsSUM.Range(&quot;D4:D&quot; &amp; i))
                  If wsSUM.Range(&quot;A4:A&quot; &amp; i).Find(What:=catID) Is Nothing Then
                      t = wsSUM.Range(&quot;D4:D&quot; &amp; i).Find(What:=Left(catID, 9)).Row
                      'Debug.Print Left(catID, 9)
  'declare row number
                          wsSUM.Rows(t + 1 &amp; &quot;:&quot; &amp; t + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                          wsSUM.Range(&quot;D&quot; &amp; t + 1).FillDown
                          wsSUM.Range(&quot;P&quot; &amp; t + 1).FillDown
                          wsSUM.Range(&quot;A&quot; &amp; t + 1).FillDown
                          With wsSUM.Range(&quot;E&quot; &amp; t + 1)
                              .FormulaR1C1 = wsp.Cells(z, 2).Value
                              .NumberFormat = &quot;@&quot;
                          End With
                          
  'get row number for period merged cells
                              RowCount = wsSUM.Range(&quot;C&quot; &amp; t).MergeArea.Rows.Count
                              FR = wsSUM.Range(&quot;C&quot; &amp; t).MergeArea.Row
                              LR = wsSUM.Range(&quot;C&quot; &amp; t).MergeArea.Row + wsSUM.Range(&quot;C&quot; &amp; t).MergeArea.Rows.Count - 1
      
  'sort data
                          With wsSUM.Sort
                              .SortFields.Add2 Key:=Range(&quot;E&quot; &amp; FR + 1 &amp; &quot;:E&quot; &amp; LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                              .SetRange Range(&quot;E&quot; &amp; FR + 1 &amp; &quot;:Q&quot; &amp; LR)
                              .header = xlYes
                              .MatchCase = False
                              .Orientation = xlTopToBottom
                              .SortMethod = xlPinYin
                              .Apply
                          End With
                          
                          i = i + 1 'with every added line is needed to broaden count
  '                    Debug.Print &quot;added to row &quot; &amp; FR &amp; &quot;; value is &quot; &amp; wsp.Cells(z, 2).Value &amp; &quot; &quot; &amp; catID
  '                    Debug.Print &quot;A4:A&quot; &amp; i
                      
                  End If
              End If
          Next n
      Next z
  </code></pre>
  <p>issue 1 - better declaration of i - I have one block of data (current quarter) starting on row 3 (header), after the first block, there are couple blank lines and another block (previous Q); I would need only rows count for current block of data, I am not able to do it without selection</p>
  <p>issue 2 - Partner IDs in final table are stored as text, in Pivot as number, so I have an issue as some IDs are starting with 0 and find part of the code won't find them; this is only issue for Partner IDs starting with 0; I tried CInt, but was not able to apply this on Range</p>
  <pre><code>sInt = CInt(wsSUM.Range(&quot;D4:D&quot; &amp; i))
  </code></pre>
  <p><a href="https://i.stack.imgur.com/SxY39.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SxY39.png" alt="issue2" /></a></p>
  <p>issue 3 - given the fact, I need 999999 on the first place and I am sorting the other data in A-Z, I don't know how to stretch merged cells in case data are added in that manner, the merged cell won't merge automatically</p>
  <p><a href="https://i.stack.imgur.com/sCpEM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sCpEM.png" alt="issue 3" /></a></p>