Detach Cells from a range

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

Is there a function in VBA to detach a sub range of cells. Eg. Have range
A1:F10 and want to remove cells A6:F10.

Thanks for any help
 
Kim

What do you mean by 'detach'? Many choices

1) Clear (Formats or formula or constants)
2) Delete
3) ReDim Range in VBA
4) Hide
5) Lock

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Hi Kim,

Kim said:
Is there a function in VBA to detach a sub range of cells. Eg. Have range
A1:F10 and want to remove cells A6:F10.
Click to expand...


See possible approaches propounded in the programming group thread:

http://tinyurl.com/6tq5v

One suggestion in that thread from Jim Wilcox, which you may be able to use,
was:

Sub Test()
NotIntersect(Selection, _
Application.InputBox("", , , , , , , 8)).Select
End Sub
'======================================

Function NotIntersect(rng As Range, x As Range) As Range
' copyright 2001-2004 Jim Wilcox
Dim y As Range
On Error Resume Next
If rng.Parent Is x.Parent Then
With x
Set y = myUnion(y, Range(Rows(1), .Rows(0)))
Set y = myUnion(y, _
Range(Rows(Rows.Count), .Rows(.Rows.Count + 1)))
Set y = Intersect(y, .EntireColumn)
Set y = myUnion(y, Range(Columns(1), .Columns(0)))
Set y = myUnion(y, _
Range(Columns(Columns.Count), .Columns(.Columns.Count + 1)))
Set y = Intersect(y, rng)
End With
Set NotIntersect = y
End If
On Error GoTo 0
End Function
'===================================

Private Function myUnion(o As Range, rng As Range) As Range
On Error Resume Next
If o Is Nothing Then
Set myUnion = rng
ElseIf rng Is Nothing Then
Set myUnion = o
Else
Set myUnion = Union(o, rng)
End If
On Error GoTo 0
End Function
'===================================
 
Back
Top