Thanks Tom appreciate the input.
--
Casey




"Tom Ogilvy" wrote:

> Unless you remove the array formula, you can not insert and delete rows
>
> --
> Regards,
> Tom Ogilvy
>
> "Casey" <Casey@discussions.microsoft.com> wrote in message
> news:E5983347-2533-408F-877D-977E70D07138@microsoft.com...
> > Hi,
> > Is it possible to add or delete rows in a range containing an array
> formula
> > and maintain the array? I have a couple of routines for I use for
> worksheets
> > that do not contain array formulas, but I know they won't work with the
> array
> > formula. Here is my current add row / delete row code, can it be modified
> to
> > handle the array formulas?
> >
> > Option Explicit
> >
> > Private Sub AddRowCA_Click()
> > Dim rngEntryBottomRow As Range
> > Application.EnableEvents = False
> > Application.ScreenUpdating = False
> > ActiveSheet.Unprotect ("geekk")
> >
> > Set rngEntryBottomRow =
> Range("Below_Entry_Bottom_RowCA").Offset(-1)
> > With rngEntryBottomRow 'rngI
> > .EntireRow.Insert
> > .Copy Destination:=.EntireRow.Offset(-1)
> > .Range("A1,C1:D1").ClearContents
> > End With
> >
> > ActiveSheet.Protect ("geekk"), DrawingObjects:=True, Contents:=True,
> > Scenarios:=True
> > Application.ScreenUpdating = True
> > Application.EnableEvents = True
> > End Sub
> >
> > Private Sub DeleteRowCA_Click()
> > Dim Response As Integer
> > Dim rngEntryBottomRow As Range
> >
> > Set rngEntryBottomRow = Range("Below_Entry_Bottom_RowCA").Offset(-1)
> >
> > 'if last detail row is blank, delete one detail row and If not empty
> > ' then msg box to explain error and exit sub.
> > If Application.WorksheetFunction.CountA(rngEntryBottomRow) > 7
> Then
> > MsgBox "You are attempting to Delete a Row that contains User
> > Input." & _
> > " Delete Row Failed", vbOKOnly + vbCritical, "Can Not Delete" & _
> > " Row with Information"
> > If Response = 0 Or 1 Then Exit Sub
> > End If
> >
> > If Application.WorksheetFunction.CountA(rngEntryBottomRow) = 7
> Then
> > Application.EnableEvents = False
> > Application.ScreenUpdating = False
> > ActiveSheet.Unprotect ("geekk")
> > With rngEntryBottomRow
> > .EntireRow.Delete
> > End With
> > End If
> >
> > ActiveSheet.Protect ("geekk"), DrawingObjects:=True,
> Contents:=True,
> > Scenarios:=True
> > Application.ScreenUpdating = True
> > Application.EnableEvents = True
> > End Sub
> > --
> > Casey
> >
> >
>
>
>