PDA

View Full Version : [SOLVED] Best way to determine if more than one cell is selected



EirikDaude
11-20-2013, 12:11 AM
I currently have some code which tries to execute if just one cell is changed in column B in a sheet.

Currently I am using the following code, in the module called by the worksheet_change event:


Sub test(r as Range, ws as Worksheet)
If Not Intersect(r, ws.Rows(2)) Is Nothing Then
If Not IsEmpty(r) And r.Count = 1 Then
(...)
End If
End If
End Sub

The problem is, if someone e.g. does a Ctrl+A on the sheet the first condition of my nested if block is fulfilled and I then get an overflow error when the Count-function tries to count all the cells in the sheet.

I've previously circumvented this problem by using an error-handler, and this time I changed my code a bit upon discovering the "Rows"- and "Columns"-properties of the Range-object:


Sub test(r as Range, ws as Worksheet)
If r.Columns.Count = 1 And Not Intersect(ws.Columns(2), r) Is Nothing Then
If Not IsEmpty(r) And r.Rows.Count = 1 Then
(...)
End If
End If
End Sub

It now works for my specific case, but unless these counts are properties Excel already have for the Range-object, it seems to be an awful lot of counting to figure out if it's a single cell that's selected. Not to mention that I'll run into the same problem again if the cell can be within a larger sized range than just column B.

So I figured I'd ask you guys here what you think is the most efficient way to determine if the size of a given range is just 1 cell, no hurry though, I've already (sort of) solved the problem for my specific case :P

Bob Phillips
11-20-2013, 02:05 AM
Excel knows how many columns and rows that the sheet has, but as r is a variable range it will need to determine that. But Excel does do a lot of things under the hood, such as usedrange, so it will be very fast to determine those two numbers.

So my advice, go with r.Rows.Count and r.Columns.Count and relax, it will not hurt you.

I tend to do the outsorting tests first, like so


Sub test(r As Range, ws As Worksheet)
If r.Rows.Count = 1 And r.Columns.Count = 1 Then
If Not Intersect(ws.Columns(2), r) Is Nothing Then
If Not IsEmpty(r) Then
(...)
End If
End If
End If
End Sub

Although this might be a bit more efficient (I mean a bit)


Sub test(r As Range, ws As Worksheet)
Dim l As Long
If Not Intersect(r, ws.Rows(2)) Is Nothing Then
If r.Rows.Count = 1 Then
If r.Columns.Count = 1 Then
If Not IsEmpty(r) Then
Debug.Print r.Address
End If
End If
End If
End If
End Sub

EirikDaude
11-20-2013, 03:20 AM
Yeah, after some further fiddling I went with something quite similar to what you have in your first example. At any rate, thanks for your feedback :)

Paul_Hossler
11-21-2013, 11:18 AM
The Range.CountLarge property is the 2007/2010 improvement for .Count



Sub test()
ActiveSheet.Cells.Select

MsgBox Selection.Rows.Count
MsgBox Selection.Columns.Count
MsgBox Selection.Cells.CountLarge
End Sub




Paul