VBA delete row after cut-&-paste

[bazzinga]

Hello,

Problem: I would like the row getting cut to be deleted automatically after being cut&paste in order to avoid lines without formulas. (Or let me know if there's any other alternative)

Context: I am using a spreadsheet that is shared with many people and on a daily basis rows gets moved around. I am trying to implement a new excel that includes a vlookup formula and don't want running cases where a line won't have a formula because it was cut to somewhere else.

Thank You,
B.

 

[bazzinga]

Not sure if I am allowed to bump a thread - just wondering if anyone know a solution for this

 

[alansidman]

How about showing us an example of your data and a mocked up solution. Provide the criteria for removing a row. Please use XL2BB function to upload samples. Do not post pictures as they cannot be manipulated. A good sample is 10-15 recocrds.

 

[bazzinga]

How about showing us an example of your data and a mocked up solution. Provide the criteria for removing a row. Please use XL2BB function to upload samples. Do not post pictures as they cannot be manipulated. A good sample is 10-15 recocrds.

Hi, not sure what to show. I want a line to be deleted automatically if I were to cut it and paste somewhere else, so there won't be lines without the formula.

So for example if I were to cut line 5 and paste it on line 25, I want line 5 to be deleted, so it doesn't get used and no populate the data.

Book1
	A	B	C	D	E	F	G
1	Info A	Info 2	Info 3	Info 4	Info 5	Info 6	Info 7
2				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
3				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
4				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
5				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
6				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
7				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
8				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
9				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
10				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
11				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
12				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
13				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
14				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
15				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
16				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
17				=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")
18
19
20
Sheet1

Cell Formulas
Range	Formula
D2:D17	D2	=IFNA(VLOOKUP($F:$F,'[sales order 2024.xlsx]Invoice No.'!$A:$D,4,FALSE),"")

 

[alansidman]

I can't see a way to do this without VBA.

Option Explicit

Sub DeleteRow()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim x As Long, y As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Application.ScreenUpdating = False
    x = InputBox("What row number to cut?")
    y = InputBox("What row number to paste?")
    s1.Range("A" & x).EntireRow.Cut s2.Range("A" & y)
    s1.Range("A" & x).EntireRow.Delete
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Complete"

End Sub