Solved: Insert-Delete Rows with formulas

[klutz]

Hello friends,

I have this work book that I use to keep track of inventory values on different locations. I attached a small sample file.

I'm looking for a macro that allows me to insert a row below an existing row that has formulas and data. The inserted row would have to have the same formulas as the row above. The blank row would only show the formaulas as I would manually insert the data values.

I would also like for if the macro can allow me to delete a row as well.

:dunno If it can be done, can there be a message box that asks me where i would like to insert or delete the row, for example, i want to Insert a new row with its formulas under "Brazil" or would it just be able to ask for the row number?

Thanks to all..

[Bob Phillips]

Public Sub AddDeleteRow()
Dim mpSelect As String
Dim mpRow As Range

mpSelect = InputBox("Choose to add (A) or delete (D) a row")
If UCase(mpSelect) = "D" Then

Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will delete", Type:=8)
mpRow.EntireRow.Delete
Else

Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will add after", Type:=8)
mpRow.Offset(1, 0).EntireRow.Insert
Cells(mpRow.Row, "N").Resize(, 3).Copy Cells(mpRow.Row + 1, "N")
End If
End Sub

[klutz]

Mr XID,

It works just fine. But, I guess I forgot to mentioned that the sheet is password protected and I get an error after trying to add or delete a row.

I tried changing the allow row insertion, deletion option when protecting sheet but the macro runs into an error prompting it can't delete rows with passowrd protect/locked cells.

Any thoughts.

I could leave it unprotected but then I run the risk someone jsut deleting the cells with formulas...

Thanks again...

[klutz]

Hi,

I'm getting a run time error typ "442" when I run the macro but I cancel before selecting anything.

it returns it here :

Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will add after", Type:=8)


Any thoughts

[Bob Phillips]

Public Sub AddDeleteRow()
Dim mpSelect As String
Dim mpRow As Range

mpSelect = InputBox("Choose to add (A) or delete (D) a row")
If mpSelect = "" Then Exit Sub
If UCase(mpSelect) = "D" Then

On Error Resume Next
Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will delete", Type:=8)
On Error GoTo 0
If Not mpRow Is Nothing Then

ActiveSheet.Unprotect Password:="password"
mpRow.EntireRow.Delete
End If
Else

On Error Resume Next
Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will add after", Type:=8)
On Error GoTo 0
If Not mpRow Is Nothing Then

ActiveSheet.Unprotect Password:="password"
mpRow.Offset(1, 0).EntireRow.Insert
Cells(mpRow.Row, "N").Resize(, 3).Copy Cells(mpRow.Row + 1, "N")
End If
End If

ActiveSheet.Protect Password:="password"
End Sub

[klutz]

Ok...It works kool, really nice, but there is an issue with the password protected sheet.

Currently the sheet is protected but with user rights to format cell font, color, size and autofilter. When I run the macro it performs es expected, it allows me to insert and or delete a row, the problem lies in that after the macro is done the worksheet is protected again but it reverts the user rights to its defualt settings, not allowing to format cells, font, size and color and autofilter.

When i select any of the cells that i wish to format I get a run time error telling me that it can't format interior cell color index..

...

...

This is so weird as I thought for sure I had posted this problem about 3 hours ago....I guess I was so tired i did not click on the reply button..Anyways, thanks you guys....

[Bob Phillips]

Just change the protect statement with



ActiveSheet.Protect Password:="password", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingCells:=True, _
AllowFiltering:=True

[klutz]

El XID, My Man

Thanx again for your help. Have a great wiken"

[klutz]

Public Sub AddDeleteRow()
Dim mpSelect As String
Dim mpRow As Range

mpSelect = InputBox("Choose to add (A) or delete (D) a row")
If mpSelect = "" Then Exit Sub
If UCase(mpSelect) = "D" Then

On Error Resume Next
Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will delete", Type:=8)
On Error GoTo 0
If Not mpRow Is Nothing Then

ActiveSheet.Unprotect Password:="password"
mpRow.EntireRow.Delete
End If
Else

On Error Resume Next
Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will add after", Type:=8)
On Error GoTo 0
If Not mpRow Is Nothing Then

ActiveSheet.Unprotect Password:="password"
mpRow.Offset(1, 0).EntireRow.Insert
Cells(mpRow.Row, "N").Resize(, 3).Copy Cells(mpRow.Row + 1, "N")
End If
End If

ActiveSheet.Protect Password:="password"
End Sub



Hey guys, the macro works great.

One other question, when I select to delete I am able to delete multiple rows but when I add i'm only able to add one row at a time withe the formulas copied into the new row. I can select various rows to add but only the first row added has the formula and not the rest.

How can the macro be changed to allow multiple row inserts while copying the formulas into them as well?

Great, apreciate all the help that has been given..I love this site. :hi:

[Bob Phillips]

Try this



Public Sub AddDeleteRow()
Dim mpSelect As String
Dim mpRow As Range
Dim mpRows As Long

mpSelect = InputBox("Choose to add (A) or delete (D) a row")
If mpSelect = "" Then Exit Sub
If UCase(mpSelect) = "D" Then

On Error Resume Next
Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will delete", Type:=8)
On Error GoTo 0
If Not mpRow Is Nothing Then

ActiveSheet.Unprotect Password:="password"
mpRow.EntireRow.Delete
End If
Else

On Error Resume Next
Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will add after", Type:=8)
On Error GoTo 0
If Not mpRow Is Nothing Then

ActiveSheet.Unprotect Password:="password"
mpRows = mpRow.Rows.Count
mpRow.Offset(mpRows, 0).EntireRow.Insert
Cells(mpRow.Row, "N").Resize(mpRows, 3).Copy Cells(mpRow.Row + mpRows, "N")
End If
End If

ActiveSheet.Protect Password:="password"
End Sub

[klutz]

Try this



Public Sub AddDeleteRow()
Dim mpSelect As String
Dim mpRow As Range
Dim mpRows As Long

mpSelect = InputBox("Choose to add (A) or delete (D) a row")
If mpSelect = "" Then Exit Sub
If UCase(mpSelect) = "D" Then

On Error Resume Next
Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will delete", Type:=8)
On Error GoTo 0
If Not mpRow Is Nothing Then

ActiveSheet.Unprotect Password:="password"
mpRow.EntireRow.Delete
End If
Else

On Error Resume Next
Set mpRow = Application.InputBox("Use the mouse to select any cell in the row you will add after", Type:=8)
On Error GoTo 0
If Not mpRow Is Nothing Then

ActiveSheet.Unprotect Password:="password"
mpRows = mpRow.Rows.Count
mpRow.Offset(mpRows, 0).EntireRow.Insert
Cells(mpRow.Row, "N").Resize(mpRows, 3).Copy Cells(mpRow.Row + mpRows, "N")
End If
End If

ActiveSheet.Protect Password:="password"
End Sub


El XID,

It does what it is intended too but I have one question or (maybe a problem).

This is what i am getting:

In row 10,11,12,13,14 columns N-S i have formulas.

Rows 15,16,17,18, columns N -S i also have formulas but different than previous rows.

If i want to add 5 addtional rows starting from row 14 this is what happens: the very first row inserted does insert with the formulas from row 14 but the remaining rows inserted are picking up the formulas from row 15 (the formula there is different than row 14).

Not sure if i wrote this statement correctly or not.:doh:

Any thoughts?

Gracias

[Bob Phillips]

So you want it to always pick up the formulae from row 1 of the selection?

Can you tell me what cells you selected in the inputbox?

[klutz]

So you want it to always pick up the formulae from row 1 of the selection?

Can you tell me what cells you selected in the inputbox?


So you want it to always pick up the formulae from row 1 of the selection?

Can you tell me what cells you selected in the inputbox?

I would like for it to pick the formula form the very first cell I select. So right now I will be selecting row 14 (fromthe sample sheet include) and want to add 5 additional rows that will pick up the formulas from row 14, but when I select row 14 and scroll down to highlt 5 additional rows the very first row inserted does pick up the formulas from row 14 but the rest are picking the formulas form from row 15 which are different than row 14.

Thanks

[Bob Phillips]

I honestly cannot see what the problem is, or how the formulae in row 15 differ from those in row 14.

[klutz]

I honestly cannot see what the problem is, or how the formulae in row 15 differ from those in row 14.



Well, rows 11-14 column Q-R cells have different formulas than rows 15-18. If I select row 14 and add one row than the inserted row (which will now become row 15) will have the formulas from row 14. Tha is fine.

But when i select to add 4 rows and my starting point is row D14 with the mouse i select continously down to D18 and add rows the formulas from the starting point D14 are not inserted in the new rows completely, instead the formulas from row d15, which are different remains the same (in row 15) and the the remaining rows have the formulas from row D14.

I thought that when you insert new rows (D14 to D18) any data that was on D15 originally gets pushed down the same amount of places or rows that were inserted, in this cased down to D19.

I'm getting confused now...:wot

Da Klutz

I attached the sample again , this time with your code...

[Bob Phillips]

Well, rows 11-14 column Q-R cells have different formulas than rows 15-18. If I select row 14 and add one row than the inserted row (which will now become row 15) will have the formulas from row 14.

That may be true, but only Columns N:P are being copied, the formulae in Q:R are being ignored, so it irrelevant whether they are the same or not.

[klutz]

That may be true, but only Columns N:P are being copied, the formulae in Q:R are being ignored, so it irrelevant whether they are the same or not.

Well, they're actually not being ignored. i modified your code on this line


Cells(mpRow.Row, "N").Resize(mpRows, 5).Copy Cells(mpRow.Row + mpRows, "N")


which allows me to start on column N and select the next 5 columns up to column R. ( I did the change and it allows me to execute it like that, not certain if it is correct or not...:( , but it works.)

It is the formulas in column R that are not being pushed down.

Take a look at the sample sheet. I ran the macro and selected a to insert 4 rows but it did not do it correctly.

Thankx again for your help..