Check if more than one cell selected

Question

I am wondering how I can check if more than one cell is celected in a range?

I have 3 cells i want to check the Ranges are "A:B" and also "D", and im trying this code but its not working for me.

If 3 - CountA(range) < 1 Then

How can I do it in anther way?

Solution 1

Something like this for selected range:

If 3-Selection.Cells.Count < 1 then

or, if there is possibility that you will have selected really a lot of cells use this one:

If 3-Selection.Cells.Countlarge < 1 Then

Solution 2

If you are using Worksheet_Change(ByVal Target As Range) or Worksheet_SelectionChange(ByVal Target As Range) then use this code:

If InStr(Target.Address, ":") > 0 Or InStr(Target.Address, ",") > 0 Or InStr(Target.Address, ";") > 0 Then

This will check if the selected range is for example:

`A1;C1` (Cells A1 and C1 are selected) or
`E1:E4` (E1 to E4 are selected)

Sometimes it is used ";" and sometimes "," so that we check both of them.

Instead of Target you can use the range that you defined in your code, for example:

If InStr(MyRange.Address, ":") > 0 Or InStr(MyRange.Address, ",") > 0 Or InStr(MyRange.Address, ";") > 0 Then

Solution 3

I think you want to try something along these lines

Dim rng1 As Range
Set rng1 = Range("A1:B1,D1")
MsgBox 3 - rng1.Cells.Count

Comments

[Leo Gurdian]

Glad you added the .CountLarge for when selecting the all cells guards you from an overflow.