Test if Selection is Non-continuous

[Ralajer]

I have several macros that fail if the selection is not continuous. Typically they are ones that use excel's built commands or operations, I will get:

Run-time error '1004'
That command cannot be used on multiple selections.​

Is there a built-in way to test if a selection is a multiple or non-continuous range, so I can switch to using a loop to instead of the faster built-in operations. I know I can write code to test the range but I have written elaborate sub's in the past only later to learn all I need was line of code.

Thanks

 

[xenou]

I believe you could use Areas.Count ... but someone tell me if I'm wrong as I'm too lazy to check!

I.e.

Set r = Range("X")
If r.Areas.Count = 1 Then 
    '//One area
Else
    '//More than one area
End If

I think you could even write your code to work on areas so you could still avoid cell by cell loops.

 

[xenou]

Okay, fired up Excel after all ...
Yes this works. And you could use the same structure even if the selection is continuous (and there is only one area).

This would let you run the same code on 1 or more areas ... as long as that is feasible given what the code does.

Sub Foo()
Dim r As Range
Dim myArea As Range
Dim i As Long

Set r = Range("A1:A2,C1:C2")

For Each myArea In r.Areas
    i = i + 1
    myArea.Value = i
Next myArea

End Sub

 

[Ralajer]

Thanks xenou. I had noticed the Areas property before but I never knew what it did. Now I know and it is definitely what I needed.

I will have to take the time and review the documentation on the range and other classes because there are probably quite a few properties and functions that would simplify my code a lot.

Well thanks again.

Rob

 

[xenou]

Cheers.
If you get a copy of the Excel 2003 VBA (Programmer's Reference) by Wrox Press it has an excellent chapter on ranges. The 2002 edition is just fine too, and of course the newer 2007 edition - I happen to have the 2003 edition.

 

[Ralajer]

I actually have the 2007 version but I've become so web centric I forgot I had it until you mentioned it, even though it was big, red and 4 feet away.