# Deselect a cell in a range?

#### XL Pro

##### Board Regular
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")

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Jay Petrulis

##### MrExcel MVP
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

##### MrExcel MVP
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...

L

#### Legacy 7259

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

Replies
8
Views
184
Replies
3
Views
191
Replies
0
Views
116
Replies
1
Views
136
Replies
3
Views
922

1,191,043
Messages
5,984,302
Members
439,882
Latest member
gerdc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back