How can I make Union cooperate with Listrows?

Question

I'm trying to incorporate this post into my project to delete many rows at once based on if they contain a certain value. However, once I get to Union, it hits me with Error 5. I'm guessing it's just the syntax since I'm not very used to working with ranges.

Public Sub IPOSheetProxy_Delete(ByVal value As String)
    Dim currentRow As ListRow
    Dim toDelete As Range
    For Each currentRow In Table.ListRows
    'table is declared elsewhere, but it's sheet1.listobjects(1)
        If currentRow.Range(columnindex:=1) = value Then
            Set toDelete = CombineRanges(currentRow.Range(), toDelete)
            'also tried this
            'For Each currentCol In Table.ListColumns
                'Set toDelete = CombineRanges(currentRow.Range(columnindex:=currentCol.Index), toDelete)
            'Next

        End If
    Next
    If Not toDelete Is Nothing Then toDelete.Delete
End Sub

here's the code for CombineRanges:

Private Function CombineRanges(ByVal source As Range, ByVal toCombine As Range) As Range
'credit Mathieu Guindon https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba/59975507#59975507
    If source Is Nothing Then
        'note: returns Nothing if toCombine is Nothing
        Set CombineRanges = toCombine
    Else
        'here's where it breaks, Err 5: invalid procedure call or argument
        Set CombineRanges = Union(source, toCombine)
    End If
End Function

Try replacing `currentRow.Range()` with `currentRow.Range`. – Ryan Wildry Apr 13 '20 at 12:48 — Ryan Wildry, Apr 13 '20 at 12:48
That was actually the first thing I tried, no dice :( – Hayden Moss Apr 13 '20 at 12:50 — Hayden Moss, Apr 13 '20 at 12:50
`Set toDelete = CombineRanges(toDelete, currentRow.Range)`. – BigBen Apr 13 '20 at 12:52 — BigBen, Apr 13 '20 at 12:52

score 2 · Accepted Answer · answered Apr 13 '20 at 13:01

First, drop the parentheses after currentRow.Range.

Then, you need to flip the arguments around in the call to CombineRanges:

Set toDelete = CombineRanges(toDelete, currentRow.Range)

The clue as to the order is the following lines:

If source Is Nothing Then
    'note: returns Nothing if toCombine is Nothing
    Set CombineRanges = toCombine

If the source (namely the first parameter) Is Nothing, then return only toCombine. You had it backwards.

How can I make Union cooperate with Listrows?

1 Answers1