Non-Adjacent Range of Cells

cdukes

New Member
Joined
Jul 26, 2010
Messages
8
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

New Member
Joined
Jul 26, 2010
Messages
8
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.
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
If you defined the range as Rng shouldn't it be

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

cdukes

New Member
Joined
Jul 26, 2010
Messages
8
Yes it should.

I used

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

which I think also works.

I apologize for the noobishness.

Thanks again.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,678
Messages
5,833,079
Members
430,190
Latest member
beaviss

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top