title: Dynamic range in VBA formula multiple worksheets
body: <p>Let consider the following piece of code, where I inserted an Excel formula to perform a very simple count.</p>
  <pre><code>dim wb as Workbook
  dim ws as Worksheet
  dim ws_a as Worksheet
  
  set wb = ThisWorkbook
  set ws = wb.Worksheets(&quot;D&quot;)
  set ws_a = wb.Worksheets(&quot;A&quot;)
  
  With ws_a
  </code></pre>
  <blockquote>
  <pre><code>.Range(&quot;c2&quot;).Formula = _
  &quot;ROWS(UNIQUE(FILTER(&quot; &amp; &quot;D&quot;! &amp; Range(&quot;c2:c400&quot;).Address &amp; &quot;,&quot; &amp; _
  &quot;IF(&quot; &amp; .Range(&quot;a2&quot;).Address(0,0) &amp; &quot; =&quot;&quot;Empty&quot;&quot;,&quot;&quot;&quot;&quot;,&quot;&quot;*&quot;&quot;&amp; &quot; &amp; .Range(&quot;a2&quot;).Address(0,0) &amp; &quot; &amp;&quot;&quot;*&quot;&quot;))&quot;
  'following part of code
  </code></pre>
  </blockquote>
  <pre><code>End With
  </code></pre>
  <p>As you can see, the Range in the Sheet &quot;D&quot; is not dynamic. I'd like to make it dynamic, taking into account the empty cells in the range. Basically, I would need to replace</p>
  <pre><code>Range(&quot;c2:c400&quot;)
  </code></pre>
  <p>with something like</p>
  <pre><code>D.Range(&quot;c2&quot;),D.Cells(D.Range(&quot;c2&quot;).SpecialCells(xlCellTypeLastCell).Row, 3))
  </code></pre>
  <p>In the attempt of concatenating strings, I definitely get lost with <code>&amp;</code> and <code>&quot;&quot;</code>, so I think this is the problem: my poor knowledge of this type of syntax.
  I've also tried to dim and set the range needed (outside the formula), but the code refers to the wrong sheet, as if it could not identify different sheets in the formula.</p>