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
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