VBA: method union of object _global failed

[kera404]

I am trying to filter out the rows having the cells in a particular column (the 'O'th column) with the value "Desired". For this I've written this code

Set PTRange = ThisWorkbook.Sheets("DumpData").Range("A1:AD1")
    Dim frow As Long
    frow = ThisWorkbook.Sheets("DumpData").Range("O" & Rows.count).End(xlUp).Row
    Set rng = ThisWorkbook.Sheets("DumpData").Range("O2:O" & frow)
    Dim count As Long
    count = 2
    For Each cel In rng
        If cel.Value = "Desired" Then
           Set PTRange = Union(PTRange, Range("A" & count & ":AD" & count))
           count = count + 1
        End If
    Next cel

But an error message pops up "Method Union of Object _Global failed" (while setting PTRange in the For Each...Next) loop.

 

[RoryA]

Welcome to the forum.

You're not qualifying the range with a worksheet in that loop:

Set PTRange = ThisWorkbook.Sheets("DumpData").Range("A1:AD1")
    Dim frow As Long
    frow = ThisWorkbook.Sheets("DumpData").Range("O" & Rows.count).End(xlUp).Row
    Set rng = ThisWorkbook.Sheets("DumpData").Range("O2:O" & frow)
    Dim count As Long
    count = 2
    For Each cel In rng
        If cel.Value = "Desired" Then
           Set PTRange = Union(PTRange, ThisWorkbook.Sheets("DumpData").Range("A" & count & ":AD" & count))
           count = count + 1
        End If
    Next cel

 

[kera404]

Welcome to the forum.

You're not qualifying the range with a worksheet in that loop:

Set PTRange = ThisWorkbook.Sheets("DumpData").Range("A1:AD1")
    Dim frow As Long
    frow = ThisWorkbook.Sheets("DumpData").Range("O" & Rows.count).End(xlUp).Row
    Set rng = ThisWorkbook.Sheets("DumpData").Range("O2:O" & frow)
    Dim count As Long
    count = 2
    For Each cel In rng
        If cel.Value = "Desired" Then
           Set PTRange = Union(PTRange, ThisWorkbook.Sheets("DumpData").Range("A" & count & ":AD" & count))
           count = count + 1
        End If
    Next cel

Thanks, it worked.