Delete Row but keep formating and formulas

[jerrypatos]

Hello all,

I need to delete the range (B:H) but keep the formula at H & G at the next row. For some reason it looses the formula.

The below code do the same thing (thanks to @Fluff) , but Insert row and working fine. Now I need to delete a row but not impact formulas at H&G

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Columns("B:H")) Is Nothing Then

Cancel = True

With Range("B" & Target.Row).Resize(, 7)

.Offset(1).Insert xlDown

.Resize(2).FillDown

End With

End If

End Sub

Worksheet

protect2.xlsx
	A	B	C	D	E	F	G	H
1		DESCR	PC	PRICE	some data	some data
2							0	0,00 €
3							0	0,00 €
4							0	0,00 €
5		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
6		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
7		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
8		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
9		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
10		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
11		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
12		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
13		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
14		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
15		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
16		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
17		JERRYPATOS	25	3.250,00 €			5	81.250,00 €
18							0	0,00 €
19							0	0,00 €
20							0	0,00 €
Sheet1

Cell Formulas
Range	Formula
G2:G20	G2	=C2*0.2
H2:H20	H2	=C2*D2

Please give me a idea what can I do.

Cheers

 

[jerrypatos]

I think that I have figure it out

The below code is working but I need to insert On error resume next to work

 If Not Intersect(Target, Columns("B:H")) Is Nothing Then
Cancel = True
On Error Resume Next
    With Range("B" & Target.Row).Resize(, 7)
      .Offset().Delete xlUp
      .Resize(, 5).FillUp
      .Offset(1).Resize(, 5).ClearContents
   End With
End If

Do anyone has an idea why is that?

Cheers