I have selected all the used ranges in my worksheet using UsedRange.Selectand wanted to unselect only the upper two ranges. Is there any way to do this?
1 Answer
Assuming the selection was made manually. If the selection was made programmatically, don't do this. Instead, just don't select the ranges you don't want to select in the first place.
There's no way to "unselect" a range. Something needs to be selected, so the way to do this is to select the range you want to end up with.
If multiple cells are selected, then the Selection object holds a Range reference that contains multiple Areas; you can iterate the Areas and determine whether an area is one of the two areas in the "upper two ranges" you don't want to keep; if they aren't add them to a new Range object using Union, and Select the union'd range once you've iterated all areas - at a relatively high abstraction level, might look like this:
Public Sub DeselectUpperAreas() Dim initialSelection As Range Set initialSelection = Application.Selection Dim finalSelection As Range Dim currentArea As Range For Each currentArea In initialSelection.Areas If Not IsUnwantedRange(currentArea) Then Set finalSelection = AppendRange(currentArea, finalSelection) End If Next If Not finalSelection Is Nothing Then finalSelection.Select
End Sub
Private Function IsUnwantedRange(ByVal target As Range) As Boolean 'TODO evaluate whether target is wanted or not; 'return True if target is unwanted in the final selection 'IsUnwantedRange = (boolean expression)
End Function
Private Function AppendRange(ByVal target As Range, ByVal merged As Range) As Range If merged Is Nothing Then Set AppendRange = target Else Set AppendRange = Union(target, merged) End If
End FunctionHow you implement the IsUnwantedRange function is unclear from the question, but one way could be to Intersect the specified target with a cell you know is inside the range you don't want selected, and return False if the intersection is Nothing.
Another way could be to pass the whole initialSelection, along with the currentArea, determine which two areas are the "upper two ranges" (by comparing their Row property, I'd guess), and return True if target is one of the two ranges you don't want to include.
Another way could be to populate an array with the Row value of each area, use WorksheetFunction.Small (once with k=1, once with k=2) to locate the two row numbers you don't want (they would be the two smaller values), and then return True if target.Row is one of these two values.
Note that legitimate uses of Selection in VBA for Excel, are very rare; depending on what you intend to do with that selection, this post might help avoiding to deal with Select and Activate completely.
