Adding or Deleteing Rows in a Range with an Array formula

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

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

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