CODEWITHSUNDEEP
vbaexcel
AMorton1989
AMorton1989

Reputation: 313

VBA - preventing Runtime Error 1004 - Method 'Union' error when using macro multiple times

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 

lastRow = ActiveSheet.UsedRange.Rows.Count

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 'ISSUE HERE 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. 

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

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. 

   Set Temprng = Excel.Union(Temprng, .Rows(Temprow))

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.

Upvotes: 1

Views: 1766

Answers (3)

David Witteried
David Witteried

Reputation: 1

I was having a similar error while building a chart using a MS Access VBA module to build a workbook. It turned out I wasn't explicate enough when I wrote (copied from macro recorder) the code. Error 1004 happens when you use the default object model for Excel (eg. Range("A1") vs Workbook(name).Sheet(name).Range or wsh.Range). In my case the problem line was for a chart object:

Dim xls as Excel.Application
Dim wsh as Worksheet
Dim cht as Chart
Dim rng1 as Range
Dim rng2 as Range
Dim rng3 as Range

''''code to set ranges and create chart object....

cht.SetSourceDate Source:=Range(xls.UNION(rng1, rng2, rng3).Address))

Correction:

cht.SetSourceDate Source:=wsh.Range(xls.UNION(rng1, rng2, rng3).Address))

You have to be carefull with macro recorder generated code using default object syntax. Make your code explicite to avoid 1004 errors.

Upvotes: 0

HangieMO
HangieMO

Reputation: 46

I'm not sure if this is your problem, but I ran across a similar issue with the Union operation and discovered an answer here. The issue for me was using the correct context for the Union call, i.e., the Application from the worksheet that the ranges were on. So in your case:

Set Temprng = Tempwks.Parent.Application.Union(Temprng, .Rows(Temprow))

Instead of:

Set Temprng = Excel.Union(Temprng, .Rows(Temprow))

Get the workbook from the worksheet (Tempwks.Parent) and the application from that workbook (Tempwks.Parent.Application).

Upvotes: 0

PermaNoob
PermaNoob

Reputation: 869

I don't see it right off the bat, so this doesn't directly solve your problem, but you can try this instead:

For Temprow = XlastRow To 1 Step -1
    If Application.WorksheetFunction.CountA(.Rows(Temprow)) = 0 Then
        .Rows(TempRow).Delete
    End If
Next TempRow

By going backwards through the rows, you don't have to worry about getting your counter messed up as the rows change numbers. And since you don't have to worry about that, you don't have to use Union.

EDIT: If you're just trying to get the TRUE last row, you can avoid all of this by using Find, i.e. 

lngLastRow = sht.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

I prefer this method over any other, but you can find more here. The find method is the last one on that page. You can also mess with the LookIn argument and change that to xlValues if that suits your needs better.

Upvotes: 2

Related Questions