Deselect a cell in a range?

[XL Pro]

I have this range set:
Set rRng = Range("G3:O3")
I want to pass a column number to it and have it deselect that cell
Say 13 is the column the rRng should now contain:
Range("G3:L3,N3:O3")

 

[Jay Petrulis]

Hi,

Not very elegant, and there is probably a more direct way, but try the following...

<pre>Sub test()
Dim rRng As Range, Col As Integer
Dim cell As Range, rRngtemp As String, i As Integer

Col = 13
Set rRng = Range("G3:O3")
For Each cell In rRng
If cell.Column <> Col Then
rRngtemp = rRngtemp & cell.Address & ","
End If
Next cell
rRngtemp = Left(rRngtemp, Len(rRngtemp) - 1)
Set rRng = Range(rRngtemp)
rRng.Select

End Sub</pre>

 

[Juan Pablo González]

Selecting is a "positive" operation, that means that you cannot "deselect" a range without remaking the entire selection. Jay's suggesting a (weird ) loop, using a string, that should work. So, short answer, there shouldn't be a "direct" way, you have to loop through the range...

 

[Legacy 7259]

If the starting range always consists of contiguous cells in one row only, then it can be done without a loop.

For example :-

Dim rng As Range, col%, rw%, col1%, col2%
Dim newRng As Range
Set rng = [G3:O3]
col = 13
If Intersect(Columns(col), rng) Is Nothing Then Exit Sub
rw = rng.Row
col1 = rng(1, 1).Column
col2 = rng.Columns.Count + col1 - 1
If col = col1 Then
Set newRng = rng.Offset(0, 1).Resize(, col2 - col1)
ElseIf col = col2 Then
Set newRng = rng.Resize(, col2 - col1)
Else
Set newRng = Union(Range(rng(1, 1), Cells(rw, col - 1)), Range(Cells(rw, col + 1), Cells(rw, col2)))
End If
This message was edited by N. Parker, jnr on 2002-08-23 01:23