Detach Cells from a range

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
 
N

Nick Hodge

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
[email protected]
 
N

Norman Jones

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
'===================================
 
A

aaron.kempf

man..

screw excel, use Access.
it is a lot more powerful for this type of thing
 
Top