CODEWITHSUNDEEP
excelvba
Passorn R.
Passorn R.

Reputation: 31

How to assign more ranges to same variable?

I want to assign more ranges to variable.

The message "Run time error :invalid procedure call or argument" appears on Set bckup = Union(.Cells(3, i), bckup) line. I have tried to use function 'range'

Set bckup = Range(.Cells(3, i), bckup)

but also got message "method range of object _global failed" or Set bckup = Range(.Cells(3, i) & bckup) and got "object variable or with block variable not set"

With Workbooks("Deactivate.xlsm").Worksheets(2)
For i = 500 To 2 Step -1

    If .Cells(3, i).Value <> "" Then

        If .Cells(7, i) = 0 Then

        'Run time error :invalid procedure call or argument
        Set bckup = Union(.Cells(3, i), bckup)

        bckup.Select
        End If

    End If
Next i
End With

I want to store all of range that meet criteria in a variable.

Upvotes: 1

Views: 38

Answers (1)

SJR
SJR

Reputation: 23081

You need to first check whether bckup contains anything as you can't union with Nothing.

With Workbooks("Deactivate.xlsm").Worksheets(2)
    For i = 500 To 2 Step -1
        If .Cells(3, i).Value <> "" Then
            If .Cells(7, i) = 0 Then
                If bckup Is Nothing Then
                    Set bckup = .Cells(3, i)
                Else
                    Set bckup = Union(.Cells(3, i), bckup)
                End If
            End If
        End If
    Next i
End With

Upvotes: 3

Related Questions