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?

 

[Phily50]

I'm aware that if I just clear the contents of the row it makes things a lot easier.

 

[Phily50]

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

Having had a think, and of course a google, can I clear the row and then shift the data up and would this still present the #REF error? Or is that basically the same as deleting a row?

 

[davesexcel]

Or possibly sorting the data, once sorted, blanks would be at the bottom.

Does the formula need to be in all the rows? Or are you only placing it in F2,G2,H2?
Place the formulas in more rows, for example:

Sheets("LookupLists").Range("F2:F10") ="=IF(CHIS!A4="""","""",(INDEX(CHIS!A4:J4,MATCH(TRUE,INDEX((CHIS!A4:J4<>0),0),0))))"

This should be rethought.
-It looks like you are creating a new list from the data sheet with formulas, is this really required?


-Normally you would not want a user to select a range.
Possibly selecting something from a dropdown, then run code based on their selection.

Hard to tell without seeing a sample.

 

[Phily50]

Or possibly sorting the data, once sorted, blanks would be at the bottom.

Does the formula need to be in all the rows? Or are you only placing it in F2,G2,H2?
Place the formulas in more rows, for example:

Sheets("LookupLists").Range("F2:F10") ="=IF(CHIS!A4="""","""",(INDEX(CHIS!A4:J4,MATCH(TRUE,INDEX((CHIS!A4:J4<>0),0),0))))"

This should be rethought.
-It looks like you are creating a new list from the data sheet with formulas, is this really required?


-Normally you would not want a user to select a range.
Possibly selecting something from a dropdown, then run code based on their selection.

Hard to tell without seeing a sample.

The removing blanks and sorting them to the bottom, seems like a good solution.

Would that involve rewriting the code (I'd expect so!)

I suppose it would just be from the delete command in the VBA. Do you have any suggestions for this?

Thanks for your help thus far

 

[Phily50]

Or possibly sorting the data, once sorted, blanks would be at the bottom.

Does the formula need to be in all the rows? Or are you only placing it in F2,G2,H2?
Place the formulas in more rows, for example:

Sheets("LookupLists").Range("F2:F10") ="=IF(CHIS!A4="""","""",(INDEX(CHIS!A4:J4,MATCH(TRUE,INDEX((CHIS!A4:J4<>0),0),0))))"

This should be rethought.
-It looks like you are creating a new list from the data sheet with formulas, is this really required?


-Normally you would not want a user to select a range.
Possibly selecting something from a dropdown, then run code based on their selection.

Hard to tell without seeing a sample.

Also to answer your other questions:

It doesn't need to be in all the rows, just from F2, G2, H2 down.

It is required to have the list elsewhere, for my uses anyway, as it needs to put into a specific order prior to displaying to the user.