VBA Union not working consistenly

[Lapwing]

I am trying to scroll through each row of a range and copying row to a new range that match a certain criteria.
If each row matches the union works correctly. If there is a row that does not match the criteria then then next row that does match does not get joined by the union command -- even though the correct path through the code is followed.

Can I emphasise - the code follows the correct path, there is no error message but rngTemp does not increase in size -- the extra row is not added. Make all rows in rngSource fit the criteria and then the Union works as expected.

Can anyone explain?

For Each wbSource In Workbooks
If wbSource.Name <> "PERSONAL.XLSB" Then
    If Not wbSource.Name = wbTarget.Name Then
        Set wsSource = wbSource.Worksheets("Risk Log")
        Set rngSource = wsSource.UsedRange
            For Each rngRow In rngSource.Rows
                If Left(rngRow.Cells(1, 2), 3) = "TS:" Then
                    If Not rngTemp Is Nothing Then
                        Set rngTemp = Union(rngTemp, rngRow) ' this is the problem row
                    Else
                        Set rngTemp = rngRow
                    End If 'temp range is empty
                End If 'a TS row
            Next 'row
    End If 'target worksheet
End If 'personal.xlsb

 

[StephenCrump]

Welcome to MrExcel.

The code looks OK to me.

Do you have many rows?

Perhaps you are running into the maximum character limit for the Range object (around 255?)

 

[Lapwing]

Thanks for the reply Stephen.
No I do not have many rows at the moment just 20 rows of dummy data.
I made rows 2 to 7 to fit the criteria and all 5 rows got joined correctly.
I then made row 3 to not fit the criteria. Row 2 was joined correctly, row three was missed correctly, the remaining rows followed the correct route but no join took place.

 

[StephenCrump]

Did you make only this one change between code is working and code is not working, i.e. in the third row of your dummy data, change "TS:xxxxx" to something else (in the 2nd column of the used range).

Because that would seem to rule out another possibility for the join not working, i.e. the VBA comparison: = "TS:" being case sensitive?

When you say "the remaining rows followed the correct route but no join took place" are you stepping through the code to check what's happening?

 

[Lapwing]

Yes, the only change I am making is to remove the TS: from the cell (ie the Risk Name. This macro is to pull out the risks that have TS: at the start of the name).
Also, yes, I am stepping through the code and watching what happens in the Locals window.
I have the rngTemp range open down to the Value2 level and can see when a new row has been added. When a new row is added Value2 closes - then when I open it a new row is there.
When I step through the code it goes to the correct line: Set rngTemp = Union(rngTemp, rngRow) but in the watch window nothing happens. Value2 does not close, no new row is added.

 

[StephenCrump]

The only way I can envisage the Union() not working is if rngTemp.Address is already around its maximum allowed length (Post #2). If that's the case, then the new row address simply won't be added to rngTemp. No error message is given.

I am guessing that although you've set up your code as if you are looping through more than one workbook, in practice you are dealing with only one workbook with a sheet called "Risk Log". If not, I'd be expecting different error messages. Which begs the question: do you have any On Error Resume Next statements in your code?

Otherwise sorry, I am temporarily out of ideas. Can you post your workbook with dummy data?

 

[Lapwing]

Thanks for your help on this Stephen. It seems that the problem was my misunderstanding of the range object in the Locals window. As you say above the code actually works.

 

[StephenCrump]

Thanks for your help on this Stephen. It seems that the problem was my misunderstanding of the range object in the Locals window. As you say above the code actually works.

Thanks for the PM and the link to your workbook.

Yes, we established the code does work in your particular circumstances. (But won't always work, as discussed above).

You could also try putting a watch on RngTemp.Address ... this will also show how the range is updated with successive row matches.

 

[StephenCrump]

(But won't always work, as discussed above).

... and as discussed in our PM exchange.

So just to further clarify for the Forum, if you do want to loop through multiple workbooks, rather than just the one, you would need to change the coding approach ...

You can't apply Union() across multiple workbooks.

 

[shg]

Stephen,

I think the Address property of a range is limited, and the length of a literal range string is limited, but you can union a range to include any number of cells:

  Dim i             As Long
  Dim r             As Range

  For i = 3 To 9999 Step 3
    If r Is Nothing Then Set r = Cells(i, "A")
    Set r = Union(r, Cells(i, "A"))
  Next i

  Debug.Print Len(r.Address), r.Cells.Count