Range Names Breaking

If you're deleting either ROW 1 or COLUMN A then the linked cells in the Name are being removed hence #REF!

Simplest perhaps is to try and revert to INDIRECT based approach for the name creation, eg:

=OFFSET(INDIRECT("'Outstanding Confirmations'!A1"),0,0,COUNTA(INDIRECT("'Oustanding Confirmations'!A:A")),28)

One way would be to move the data table down a few rows and adjust the OFFSET formula

Data starts in row 4.

=OFFSET(Sheet1!$A$1,3,0,COUNTA(Sheet1!$A:$A),4)

DonkeyOte

Firstly thank you for your answer and very prompt reply.

I've tried this and by using your piece of code now, it only selects the first row. (both before and after sorting the data), rather than the whole list as before.

Only has to be NOT row 1.

Having users can cause you all sorts of problems

Try using this: =INDEX('Outstanding Confirmations'!$A:$A,1):INDEX('Outstanding Confirmations'!$AB:$AB,COUNTA('Outstanding Confirmations'!$A:$A))

drchris, I can only get the #REF with romp's suggestion if i delete columns A:AB.
Sorting has no effect.

Perhaps you should post an example workbook with any code you are using.

Which version of Excel? I find it odd that sorting would cause that with a whole column reference!

So you are deleting not sorting.

And you are deleting an item that the formula refers to. So, as DO pointed out, deleting the range/row/column that is referenced will cause #REF

Since you are using code anyway, why not create the name after you do the delete?

Andy:
Indeed. I mentioned that in my initial post see above:
'....and by pressing a button sort_data it does a lot of sorting, filtering, deleting and so on'.

And on which basis I based my approach in post # 2 ?
INDIRECT will not generate #REF errors if you delete the rows nor even the sheet in full assuming it's replaced like-for-like.

You could still use INDEX if you were to reference the entire sheet and set column numbers appropriately but I believed INDIRECT to be the simpler approach and based on my own interpretation of things I've read regards how Names are evaluated I felt it whould not lead to any significant drawbacks over INDEX method
(despite INDIRECT being a Volatile function in it's own right and INDEX not)

Why use a named range, in this instance?

Sub DeleteRows()
    
    With Sheet1
        With .Range("A1").CurrentRegion
            .AdvancedFilter action:=xlFilterInPlace, criteriarange:=Range("deletecriteria")
            .Offset(1, 0).Resize(.Rows.Count - 1, 4).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        .ShowAllData
    End With
    
End Sub

Macro recorder may help with adding/deleting a named range

row 1 only with indirect is due to a typo in sheet name

=OFFSET(INDIRECT("'Outstanding Confirmations'!A1"),0,0,COUNTA(INDIRECT("'Outstanding Confirmations'!A:A")),28)

Apologies for the typo - I should have checked (this is what happens when you do things on the fly!)

I'm with both Andy & R though - given you're using VBA it would probably be best to resolve your names via that route.

And yes - it's chilly in Suffolk - my boiler is presently drinking heating oil likes it's going out of fashion ... might be cheaper to just burn some five pound notes in the middle of the room

Originally Posted by DonkeyOte

my boiler is presently drinking heating oil likes it's going out of fashion ... might be cheaper to just burn some five pound notes in the middle of the room

Sounds like my Aga!