| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding or removing rows
Hi all.
I have got a macro that I use for adding and deleting rows while sheet is protected (Gord Dibben helped me with, and is working well). I do have one thing that may be a problem and that is there are certain rows I dont want to be changed. These are the last row of each section and have the sub totals. The problem is that if one of these rows get deleted by misstake it will upset the final total at end of the sheet. Is there anyway to stop these from being upset and protected. Regards Chris |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding or removing rows
Post your code...
-- HTH... Jim Thomlinson "Chris" wrote: Hi all. I have got a macro that I use for adding and deleting rows while sheet is protected (Gord Dibben helped me with, and is working well). I do have one thing that may be a problem and that is there are certain rows I dont want to be changed. These are the last row of each section and have the sub totals. The problem is that if one of these rows get deleted by misstake it will upset the final total at end of the sheet. Is there anyway to stop these from being upset and protected. Regards Chris |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding or removing rows
Hi Jim
I have sections of about 10 rows that I can add or remove rows. These have formulas in witch this code comands. Then on the next row below I have totals from the rows above with more formulas, I dont want to be able to add or remove if row is selected by mistake. Regards Chris Sub testme() Call InsertRowsAndFillFormulas End Sub Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) Dim myCell As Range If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", _ Title:="Add Rows", _ Default:=1, Type:=1) If vRows = False Then Exit Sub End If End If ActiveSheet.Unprotect Set myCell = ActiveCell myCell.Offset(1).Resize(vRows).EntireRow.Insert myCell.EntireRow.AutoFill _ Destination:=myCell.Resize(vRows + 1).EntireRow, _ Type:=xlFillDefault On Error Resume Next myCell.Offset(1, 0).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents On Error GoTo 0 ActiveSheet.Protect _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Chris" wrote: Hi all. I have got a macro that I use for adding and deleting rows while sheet is protected (Gord Dibben helped me with, and is working well). I do have one thing that may be a problem and that is there are certain rows I dont want to be changed. These are the last row of each section and have the sub totals. The problem is that if one of these rows get deleted by misstake it will upset the final total at end of the sheet. Is there anyway to stop these from being upset and protected. Regards Chris |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding or removing rows
"Chris" wrote: Hi Jim I have sections of about 10 rows that I can add or remove rows. These have formulas in witch this code comands. Then on the next row below I have totals from the rows above with more formulas, I dont want to be able to add or remove if row is selected by mistake. this is the one I use for inserting rows and I have another for deleting rows. Regards Chris Sub testme() Call InsertRowsAndFillFormulas End Sub Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) Dim myCell As Range If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", _ Title:="Add Rows", _ Default:=1, Type:=1) If vRows = False Then Exit Sub End If End If ActiveSheet.Unprotect Set myCell = ActiveCell myCell.Offset(1).Resize(vRows).EntireRow.Insert myCell.EntireRow.AutoFill _ Destination:=myCell.Resize(vRows + 1).EntireRow, _ Type:=xlFillDefault On Error Resume Next myCell.Offset(1, 0).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents On Error GoTo 0 ActiveSheet.Protect _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Chris" wrote: Hi all. I have got a macro that I use for adding and deleting rows while sheet is protected (Gord Dibben helped me with, and is working well). I do have one thing that may be a problem and that is there are certain rows I dont want to be changed. These are the last row of each section and have the sub totals. The problem is that if one of these rows get deleted by misstake it will upset the final total at end of the sheet. Is there anyway to stop these from being upset and protected. Regards Chris |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding or removing rows
Sorry about taking so long. Try this...
Sub testme() Call InsertRowsAndFillFormulas End Sub Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) Dim myCell As Range Set myCell = ActiveCell If InStr(1, Cells(myCell.Row, "A").Value, "Total") 0 Then MsgBox "Total Line" Exit Sub End If If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", _ Title:="Add Rows", _ Default:=1, Type:=1) If vRows = False Then Exit Sub End If End If ActiveSheet.Unprotect myCell.Offset(1).Resize(vRows).EntireRow.Insert myCell.EntireRow.AutoFill _ Destination:=myCell.Resize(vRows + 1).EntireRow, _ Type:=xlFillDefault On Error Resume Next myCell.Offset(1, 0).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents On Error GoTo 0 ActiveSheet.Protect _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub -- HTH... Jim Thomlinson "Chris" wrote: Hi Jim I have sections of about 10 rows that I can add or remove rows. These have formulas in witch this code comands. Then on the next row below I have totals from the rows above with more formulas, I dont want to be able to add or remove if row is selected by mistake. Regards Chris Sub testme() Call InsertRowsAndFillFormulas End Sub Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) Dim myCell As Range If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", _ Title:="Add Rows", _ Default:=1, Type:=1) If vRows = False Then Exit Sub End If End If ActiveSheet.Unprotect Set myCell = ActiveCell myCell.Offset(1).Resize(vRows).EntireRow.Insert myCell.EntireRow.AutoFill _ Destination:=myCell.Resize(vRows + 1).EntireRow, _ Type:=xlFillDefault On Error Resume Next myCell.Offset(1, 0).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents On Error GoTo 0 ActiveSheet.Protect _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Chris" wrote: Hi all. I have got a macro that I use for adding and deleting rows while sheet is protected (Gord Dibben helped me with, and is working well). I do have one thing that may be a problem and that is there are certain rows I dont want to be changed. These are the last row of each section and have the sub totals. The problem is that if one of these rows get deleted by misstake it will upset the final total at end of the sheet. Is there anyway to stop these from being upset and protected. Regards Chris |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding or removing rows
Hi Jim
I coppied the code and tried it but it didn't help. Can I select a group of rows I could work with and leave out the ones I need left as is. Eg select rows 1 to 10 then 12 to 20 ,22 to 30 etc. Regards Chris "Jim Thomlinson" wrote: Sorry about taking so long. Try this... Sub testme() Call InsertRowsAndFillFormulas End Sub Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) Dim myCell As Range Set myCell = ActiveCell If InStr(1, Cells(myCell.Row, "A").Value, "Total") 0 Then MsgBox "Total Line" Exit Sub End If If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", _ Title:="Add Rows", _ Default:=1, Type:=1) If vRows = False Then Exit Sub End If End If ActiveSheet.Unprotect myCell.Offset(1).Resize(vRows).EntireRow.Insert myCell.EntireRow.AutoFill _ Destination:=myCell.Resize(vRows + 1).EntireRow, _ Type:=xlFillDefault On Error Resume Next myCell.Offset(1, 0).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents On Error GoTo 0 ActiveSheet.Protect _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub -- HTH... Jim Thomlinson "Chris" wrote: Hi Jim I have sections of about 10 rows that I can add or remove rows. These have formulas in witch this code comands. Then on the next row below I have totals from the rows above with more formulas, I dont want to be able to add or remove if row is selected by mistake. Regards Chris Sub testme() Call InsertRowsAndFillFormulas End Sub Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) Dim myCell As Range If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", _ Title:="Add Rows", _ Default:=1, Type:=1) If vRows = False Then Exit Sub End If End If ActiveSheet.Unprotect Set myCell = ActiveCell myCell.Offset(1).Resize(vRows).EntireRow.Insert myCell.EntireRow.AutoFill _ Destination:=myCell.Resize(vRows + 1).EntireRow, _ Type:=xlFillDefault On Error Resume Next myCell.Offset(1, 0).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents On Error GoTo 0 ActiveSheet.Protect _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Chris" wrote: Hi all. I have got a macro that I use for adding and deleting rows while sheet is protected (Gord Dibben helped me with, and is working well). I do have one thing that may be a problem and that is there are certain rows I dont want to be changed. These are the last row of each section and have the sub totals. The problem is that if one of these rows get deleted by misstake it will upset the final total at end of the sheet. Is there anyway to stop these from being upset and protected. Regards Chris |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding or removing rows
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding/Removing Rows Automatically. | Excel Discussion (Misc queries) | |||
| Automatically adjusting # of entries by adding/removing rows | Excel Worksheet Functions | |||
| Adding and Removing Extra Pages | Excel Discussion (Misc queries) | |||
| Adding or removing additonal links | Excel Discussion (Misc queries) | |||
Hybrid Mode
