Consulting

Results 1 to 4 of 4

Thread: Best way to determine if more than one cell is selected

  1. #1

    Question Best way to determine if more than one cell is selected

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •