Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleteing some Rows | Excel Discussion (Misc queries) | |||
Deleteing Rows | Excel Programming | |||
Deleteing Duplicate Rows??? | Excel Programming | |||
Deleteing Rows | Excel Programming | |||
Deleteing Blank Rows | Excel Programming |