title: How to do union of more than two string using VBA Excel without encountering runtime error?
body: <p>Hi and hello everyone.</p>
  
  <p>I'm a total novice in this VBA coding and I'm not sure why have I encountered the following error: </p>
  
  <blockquote>
    <p>VBA - Runtime error 1004</p>
  </blockquote>
  
  <p>before this, it was running well but as I added more to my range, this error popped out and the debugger keep pointing to <code>Set myRng = Application.Union(.Range(myCopy), .Range(myCopy2))</code>.</p>
  
  <p>I don't know how to fix this part of code and here I provide the full code</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>Sub UpdateLogWorksheet()
  
      Dim historyWks As Worksheet
      Dim inputWks As Worksheet
  
      Dim nextRow As Long
      Dim oCol As Long
  
      Dim myRng As Range
      Dim myCopy As String
      Dim myCopy2 As String
      Dim myCell As Range
      
      'cells to copy from Input sheet - some contain formulas
      myCopy = "D10, D12, D14, D16, D18, D20, D22, D24, D26, D28, D30, D32, D34, D36, D38, D40, D42, D46, D48, D50, D52, D54, D56, D58, D60, D62, D64, D66, D68, D70,D72, D74, D78, D80, D82, D86, D88, D90, D92, D94, D96, D98, D100, D102, D104, D106, D108, D110, D113"
      myCopy2 = "D115, D119, D121, D123, D125, D127, D129, D131, D133, D137, D139, D141, D143, D145, D147, D149, D151, D153, D155, D159, D163, D168, D170, D174, D178, D182, D184, D186, D191, D193, D195, D199, D201, D205, D203, D207, D209, D211, D215, D217, D219, D221, D223"
      
      
      Set inputWks = Worksheets("Input")
      Set historyWks = Worksheets("IncidentDatabase")
  
      With historyWks
          nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
      End With
  
      With inputWks
          Set myRng = Application.Union(.Range(myCopy), .Range(myCopy2))
      End With
      
  
      Set inputWks = Worksheets("Input")
      Set historyWks = Worksheets("IncidentDatabase")
  
      With historyWks
          nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
      End With
  
      With inputWks
          Set myRng = Union(.Range(myCopy), .Range(myCopy2))
  
          If Application.CountA(myRng) &lt;&gt; myRng.Cells.Count Then
              MsgBox "Please fill in all the cells!"
              Exit Sub
          End If
      End With
  
      With historyWks
          With .Cells(nextRow, "A")
              .Value = Now
              .NumberFormat = "mm/dd/yyyy hh:mm:ss"
          End With
          .Cells(nextRow, "B").Value = Application.UserName
          oCol = 3
          For Each myCell In myRng.Cells
              historyWks.Cells(nextRow, oCol).Value = myCell.Value
              oCol = oCol + 1
          Next myCell
      End With
      
      'clear input cells that contain constants
      With inputWks
        On Error Resume Next
           With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
                .ClearContents
                Application.GoTo .Cells(1) ', Scroll:=True
           End With
        On Error GoTo 0
      End With
  End Sub</code></pre>
  </div>
  </div>
  </p>
  
  <p>I know that there's already countless of questions regarding VBA run time error 1004 but as I keep reading through them, I didn't find the answer.</p>
  
  <p>Every help and guidance is greatly appreciated,</p>