Delete row if #REF! shown

[ipbr21054]

Hi,
I am using this simple code =DATABASE!A202 & son on.
I have noticed that if nothing is in the cell in question then on the worksheet where there should be a value etc i see #REF !

Can we use some kind of code so that row is then deleted should #REF ! be in any cell in Column A

Thanks

 

[Fluff]

Will you just have #REF! errors, or are there other error that you want to keep?

 

[ipbr21054]

This is the only one.
As items get deleted this #REF! Is then shown.
Let’s say it was looking for data in cell Z50 but then I delete Z50 this is when it appears.
If I look either side of this row I see code for Z49 in the row before and Z50 in the row after so this row which has the #REF! In is then redundant and isn’t needed or upsetting the code.
Hence why I would like it gone.

 

[Rick Rothstein]

This is the only one.
As items get deleted this #REF ! Is then shown.

Just to clarify... the question Fluff was asking is could there ever be any other errors in Column A besides #REF ! errors?

Also are you looking for a VBA solution or a manual solution?

 

[Rick Rothstein]

Just to clarify... the question Fluff was asking is could there ever be any other errors in Column A besides [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] ! errors?

Also are you looking for a VBA solution or a manual solution?

Assuming you only will ever have #REF ! errors and no other type of errors and that a VBA solution is desired...

Sub DeleteRefErrors()
  Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End Sub

 

[ipbr21054]

Hi,
To apply this code is it right click on name tab and paste ?

 

[ipbr21054]

I see its a macro for a button.
Is there an automatic way so it monitors the sheet ?

Thanks

 

[ipbr21054]

Can you advise please the correct way to write this so when the worksheet is open it will run.

Private Sub Workbook_Open()With Worksheets("INV DETAILS")
  Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
  End With
End Sub

 

[Fluff]

Simply put a full stop before Columns

 

[ipbr21054]

Hi,
Ive put the code with another in the This workbook as shown below.
When i open the workbook i get a pop up message saying No cells found ?

Having said that is it best if i put the code back as it was and have the code run each time the worksheet is open,if so please advise.

Thanks

Private Sub Workbook_Open()With Worksheets("HONDA SHEET")
    .Activate
    Range("A13").Select
    ActiveWindow.ScrollRow = 13
    End With
    With Worksheets("INV DETAILS")
  .Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
  End With
    End Sub