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]

Is the code you posted in post#16 the only code in the open event, or have you removed other code to "simplify" things?
The reason I ask is the as I stated the On Error resume next should prevent the error message, unless VBA is already handling another error

 

[ipbr21054]

Just making sure that im on the same page as you.

Alt & F11
Double click ThisWorkBook


This is all the code now shown on the right.

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)


    With Sheets("HONDA SHEET").Range("C1:F17").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
Sheets("HONDA SHEET").Range("A2,A17").ClearContents
Application.EnableEvents = True
End Sub
Private Sub Workbook_Open()
   Worksheets("HONDA SHEET").Activate
   Range("A13").Select
   ActiveWindow.ScrollRow = 13
   With Worksheets("INV DETAILS")
      On Error Resume Next
      .Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
      On Error GoTo 0
   End With
End Sub

If i right click on the INV DETAILS tab & click on View Code its empty.

Thanks

 

[Fluff]

Don't understand this.
Try closing down Xl completely, then open the workbook, do you still get the error message?

 

[ipbr21054]

Yes i do

 

[Fluff]

In that case I have no idea what is happening.

 

[ipbr21054]

ok
Thanks anyway,i will just click on end.

Thanks

 

[ipbr21054]

Evening,
Fluff,Ive just had a though.

Its more of a workaround so that message isnt shown when i open the worksheet.

How about we put #REF! in a cell when the worksheet closes each time.
That way each time it opens we shouldnt see that message as it will have a #REF! to delete the row

The row would need to be far away from my work like row 20000 etc ?

What do you think.
If possible how do we do it.

Thanks

 

[Fluff]

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Application.EnableEvents = False
   Sheets("HONDA SHEET").Range("A2,A17").ClearContents
   Sheets("Inv Details").Range("A" & Rows.Count).End(xlUp).Offset(4).Value = "#N/A"
   Application.EnableEvents = True

End Sub

 

[ipbr21054]

I get the same message and this is shown in yellow when i debug
.Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

I also see the #NA Being put in 4th row after my last row with data in.

If this will not work without that error then maybe have a macro to delete the #REF ! upon that worksheet when activated,will this then delete them once i open that sheet ?

Just saves me clicking a button every time.

 

[Fluff]

What happens if you comment out the lines in red, do you still get the error?

Private Sub Workbook_Open()
  [COLOR=#ff0000] Worksheets("HONDA SHEET").Activate
   Range("A13").Select
   ActiveWindow.ScrollRow = 13[/COLOR]
   With Worksheets("INV DETAILS")
      On Error Resume Next
      .Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
      On Error GoTo 0
   End With
End Sub