Avoid the #REF error when copying and deleting rows

[Phily50]

So having had one question answered, I now run into another problem. I have this code-

Private Sub CCOk_Click()

Dim Answer As Integer
Dim NewSht As Worksheet
Dim NewRow As Long
Dim ThisRow As Long

Answer = MsgBox("You have selected to cancel " & UCase(ActiveCell.Value) & vbNewLine & vbNewLine & "This will move the CHIS to Cancelled and remove ALL data from this page", vbExclamation + vbYesNo + vbDefaultButton2, "Are you sure?")

    If Answer = vbYes Then
        Set NewSht = Sheets("Cancelled")

            Application.EnableEvents = False
            Application.Calculation = xlCalculationManual
            ThisRow = ActiveCell.Row
               
                NewRow = NewSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

                NewSht.Cells(NewRow, "A").Value = Cells(ThisRow, "A").Value
                NewSht.Cells(NewRow, "B").Value = Cells(ThisRow, "B").Value
                NewSht.Cells(NewRow, "C").Value = Cells(ThisRow, "C").Value
                NewSht.Cells(NewRow, "D").Value = Cells(ThisRow, "D").Value
                NewSht.Cells(NewRow, "E").Value = Cells(ThisRow, "E").Value
                                 
                ActiveCell.EntireRow.Delete
          
                    Application.CutCopyMode = False
                    Application.EnableEvents = True
                    Application.Calculation = xlCalculationAutomatic
        Else
        'do nothing
    End If
   
frmCancelCHIS.Hide
End Sub

...that thanks to GWteB now works perfectly.


However, I have since run into another problem that I'm sure is very easy to solve for a non dunce like me.


That bit of code deletes the row that the data was in. In another sheet I have the following formulas:

=IF(SHEET2!A4="","",(INDEX(SHEET2!A4:J4,MATCH(TRUE,INDEX((SHEET2!A4:J4<>0),0),0))))

=IF(SHEET2!A4="","",(LOOKUP(2,1/(SHEET2!A4:I4<>""),SHEET2!A4:I4)))

=IF(SHEET2!A4="","",(LOOKUP(2,1/(SHEET2!A4:J4<>""),SHEET2!A4:J4)))

Which basically looks up the 1st and last empty cells in the specified ranges and pulls it into a list for use elsewhere. When I delete the row using that function above, I encounter the #REF error because the row no longer exists- I would have thought if I was deleting Row 5 for instance, that row 6 becomes row 5, 7 becomes 6 and so on, and therefore the code would still function properly. Is there a way to resolve this by changing the formula?

 

[davesexcel]

You should be able to replace the formulas after deleting the ranges.
For example:

Range("A1") = "=IF(Sheet2!A4="""","""",(INDEX(Sheet2!A4:J4,MATCH(TRUE,INDEX((Sheet2!A4:J4<>0),0),0))))"
Range("B2") = "=IF(Sheet2!A4="""","""",(LOOKUP(2,1/(Sheet2!A4:I4<>""""),Sheet2!A4:I4)))"
Range("C3") = "=IF(Sheet2!A4="""","""",(LOOKUP(2,1/(Sheet2!A4:J4<>""""),Sheet2!A4:J4)))"

 

[Phily50]

Sorry the formula is in another sheet, not in the one that is being deleted. They just reference the range where the rows are being deleted and when the rows are deleted they throw up the ref error as one of the rows no longer exists.

 

[Phily50]

Where would that code actually go?

 

[davesexcel]

You are deleting ranges that a formula references, use my example and place the formula in the correct sheet and range after deleting the referenced range.

Sheets("Sheet1").range("A1")=your formula

 

[Phily50]

You are deleting ranges that a formula references, use my example and place the formula in the correct sheet and range after deleting the referenced range.

Sheets("Sheet1").range("A1")=your formula

Thanks again for your response. So where would I put that within the VBA code? Presumably after the delete command? And just so I understand what you mean, the "Sheet1" is the worksheet where the formula is located and the range is the column that the formula is?

 

[davesexcel]

Correct, vba runs line by line.

 

[Phily50]

So I've been trying to do this for a couple days on and off but still for the life of me cannot get it to work. I pasted this:

Sheets("LookupLists").Range("F2") = "=IF(CHIS!A4="""","""",(INDEX(CHIS!A4:J4,MATCH(TRUE,INDEX((CHIS!A4:J4<>0),0),0))))"
Sheets("LookupLists").Range("G2") = "=IF(CHIS!A4="""","""",(LOOKUP(2,1/(CHIS!A4:I4<>""""),CHIS!A4:I4)))"
Sheets("LookupLists").Range("H2") = "=IF(CHIS!A4="""","""",(LOOKUP(2,1/(CHIS!A4:J4<>""""),CHIS!A4:J4)))"

after the

ActiveCell.EntireRow.Delete

command within the code, and it doesn't work. It doesn't throw up any debug errors (not that I would know what they were anyway :D ), but the formulas within the sheet "LookupLists" in columns F, G and H still return the #REF error once the cell in "CHIS" is deleted. which throws other cells out within the workbook that rely on these being right. Is there an easy fix or should i confine my PC to the bin

 

[davesexcel]

Do you need to delete the row?, could you not clearcontents instead?

 

[Phily50]

I do, I'd prefer there not to be gaps in the data.