title: VBA - preventing Runtime Error 1004 - Method 'Union' error when using macro multiple times
body: <p>I have a large macro which is editing a number of excel spreadsheets in a folder. At one stage of the macro, I am deleting any unused rows on the spreadsheet, including those rows which have no content, but due to the formatting appear in </p>
  
  <pre><code>lastRow = ActiveSheet.UsedRange.Rows.Count
  </code></pre>
  
  <p>When I loop through the large macro for each spreadsheet, and reach the section of code, I encounter a Runtime Error 1004 - Method 'Union'. I have indicated the error below <code>'ISSUE HERE</code> where I am having difficulties assigning an Excel.Range variable. It works the first time, but then crashes the second. I have tried initialising the variable as Null following the loop, but that doesn't work. I also considered the possibility of dynamically changing the name of the variable, but I'm not sure how to do that either. </p>
  
  <pre><code>Dim Tempwks As Excel.Worksheet
          Dim Temprng As Excel.Range
          Dim Temprow As Long
          Dim XlastRow As Long
  
      ''''''''''''''''
  
          Set Tempwks = Excel.ActiveSheet
          XlastRow = lastRow
  
          With Tempwks
  
              For Temprow = 1 To XlastRow
                  If Application.WorksheetFunction.CountA(.Rows(Temprow)) = 0 Then
  
                      If Temprng Is Nothing Then
                          Set Temprng = .Rows(Temprow)
                      Else
  
                      ' ISSUE HERE
                          Set Temprng = Excel.Union(Temprng, .Rows(Temprow))
  
                      End If
  
                  End If
              Next Temprow
  
          End With
  
          If Not Temprng Is Nothing Then
              Call Temprng.EntireRow.Delete
          End If
  </code></pre>
  
  <p>EDIT -- Just to confirm, there is nothing wrong with the functionality of this code, it does what is required, it's just that when I run it multiple times, it comes up with the Runtime Error 1004 on the following specific line. </p>
  
  <pre><code>   Set Temprng = Excel.Union(Temprng, .Rows(Temprow))
  </code></pre>
  
  <p>If this macro ran again and the variable name changed from Temprng to Temprng2, it would work. The issue seems to be that once the variable has been Set once, it cannot be overwritten.</p>