Easy way to test range as single cell

  • Thread starter Thread starter Dallman Ross
  • Start date Start date
D

Dallman Ross

I'm trying to come up with an easy way in VBA to know if
the selected range is one single cell only -- and from a
restricted range of cells. I suspect this is trivially
easy to do, but the how of it escapes me at this moment.
Ideas gladly solicited.

dman
 
I'm trying to come up with an easy way in VBA to know if
the selected range is one single cell only -- and from a
restricted range of cells. I suspect this is trivially
easy to do, but the how of it escapes me at this moment.
Ideas gladly solicited.

dman
Click to expand...

If the user has already selected the range then it would be:

if Selection.Cells.Count > 1 then
Msgbox "More than one cell is selected"
end if

James
 
You might try

' ensure a Range is selected
If TypeOf Selection Is Excel.Range Then
If Selection.Cells.Count = 1 Then
' one cell selected
If Not Application.Intersect(Selection, Range("A1:A10")) Is Nothing
Then
' range is within A1:A10
' do something
Else
' not in A1:A10
End If
Else
' more than one cell selected
End If
Else
' selection is not a range (perhaps a Shape or something)
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Hi

Try this:

Option Explicit

Dim isect As Variant
Dim rRange As Range

Sub test()
Set rRange = Range("A1:C5")
If Selection.Rows.Count * Selection.Columns.Count > 1 Then MsgBox "Multiple
cells selected!"

Set isect = Application.Intersect(Selection, rRange)
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If

End Sub
 
Thank you, Chip, this looks very good indeed. Per's similar
approach was also interesting to see. Thanks to you both!

dman
 
Back
Top