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(rngEntryBotto mRow) 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(rngEntryBotto mRow) = 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

Adding or Deleteing Rows in a Range with an Array formula
 

Unless you remove the array formula, you can not insert and delete rows

--
Regards,
Tom Ogilvy

"Casey" wrote in message
...
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(rngEntryBotto mRow) 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(rngEntryBotto mRow) = 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

Adding or Deleteing Rows in a Range with an Array formula
 

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" wrote in message
...
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(rngEntryBotto mRow) 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(rngEntryBotto mRow) = 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