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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,924
Messages
5,514,198
Members
408,990
Latest member
fresse68

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top