 <p>There must be a nicer solution, but this is the one that quickly popped into mind:</p> <pre><code>set source = range(Rows(1).address &amp; "," &amp; Rows(emptyrow).address).SpecialCells(xlCellTypeVisible) </code></pre> <p>Disgusting, but works (if I understood what you needed correctly).</p> <p>Edit: found another solution:</p> <pre><code>set source = Application.Union(rows(1),rows(emptyrow)).SpecialCells(xlCellTypeVisible) </code></pre>