title: MS Excel VBA - Selecting multiple distinct dynamic ranges
body: <p>I need to copy the data in various columns that are not next to each other and that will have a variable number of rows (but that variable will have the same value for each column).</p>
  
  <p>My code so far is as follows:</p>
  
  <pre><code> Set MyXL = CreateObject("Excel.Application")
   MyXL.Workbooks.Add
   MyXL.Worksheets(1).Name = SheetName 
  
   Set shtData = Worksheets("Data")
   rngDataRowCount = shtData.Range("A1048576").End(xlUp).Row
   Set rngData = shtData.Range("A1:I" &amp; rngDataRowCount)
   rngData.Copy
  
   MyXL.Worksheets(SheetName).Activate
   MyXL.Worksheets(SheetName).Paste
   Range("A1").Select
  </code></pre>
  
  <p>Which works fine as long as the columns are next to each other but doesn't allow me to add others.</p>
  
  <p>I was hoping I could do something like:</p>
  
  <pre><code>     Set rngData = shtData.Range("A1:I, N1:N, P1:R" &amp; rngDataRowCount)
  </code></pre>
  
  <p>But this statement fails. Alternatively is there any way I can Set multiple ranges and then append to the clipboard? Any help is, of course, appreciated.</p>
  
  <p><strong>EDIT:</strong> To clarify what I'm trying to achieve let's say I have the following table:</p>
  
  <pre><code> _________________________________________________________
   | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 |
   _________________________________________________________
   | q    | w    | e    | r    | t    | y    | u    | i    |
   _________________________________________________________
   | q    | w    | e    | r    | t    | y    | u    | i    |
   _________________________________________________________
  </code></pre>
  
  <p>I want to be able to select a range say col1, col2, col5 and col8 so that it copies all filled cells in those columns in one go so that pasting gives me this:</p>
  
  <pre><code> _____________________________
   | col1 | col2 | col5 | col8 |
   _____________________________
   | q    | w    | t    | i    |
   _____________________________
   | q    | w    | t    | i    |
   _____________________________
  </code></pre>