Non-Adjacent Range of Cells

[cdukes]

Hello:

I am attempting to select a range of non-adjacent cells for later use in a macro. "i" is set to designate the appropriate row.

Set rng = Sheets("name").Range(Cells(i, 65), Cells(i, 69), Cells(i, 73), _
Cells(i, 77), Cells(i, 81), Cells(i, 85), Cells(i, 89), Cells(i, 94), Cells(i, 99))

Unfortunately, when run I receive the following error:

"Run time error 450: Wrong number of arguments or invalid property assignment"

Is my syntax with .Range(Cells(), Cells(), Cells()) incorrect, or do I have another problem?

Thanks for any help.

 

[VoG]

Try

With Sheets("name")
    Set Rng = Union(.Cells(i, 65), .Cells(i, 69), .Cells(i, 73), _
    .Cells(i, 77), .Cells(i, 81), .Cells(i, 85), .Cells(i, 89), .Cells(i, 94), .Cells(i, 99))
End With

 

[cdukes]

VoG:
Thanks so much for the timely response. I really appreciate the help.

The union method appears to work, in so far as inserting values into the range is successful. However, it is very possible that the cells I am referencing will be empty. I've seen in a couple places there may be issues using a union on empty cells.

Later in my code I use the range to determine if any of the cells have been filled:

If Application.WorksheetFunction.CountA(Sheets("name").Range(range)) > 0 Then

Which gives me this error:

"Application Defined or Object Defined Error"

Any thoughts on if this is problem with the union/range or with WorksheetFunction??

Thanks again.

 

[VoG]

If you defined the range as Rng shouldn't it be

If Application.WorksheetFunction.CountA(Sheets("name").Rng) > 0 Then

 

[cdukes]

Yes it should.

I used

If Application.WorksheetFunction.CountA(range) > 0 Then

which I think also works.

I apologize for the noobishness.

Thanks again.