Hi

Try this

Code:
Option Explicit

Sub kTest()
    
    Dim i   As Long
    Dim r   As Long
    Dim j   As Long
    Dim v   As Variant
    Dim k   As Variant
    
    Const ResizeCol As Long = 8 '<< adjust this value with actual column count
    Const ReplaceV  As String = "####"
    
    v = Array("% Agree", "Mean score")
    k = Array(999999999, 999999998)
    
    Application.ScreenUpdating = 0
    With ThisWorkbook
        For i = 1 To .Worksheets.Count
            With .Worksheets(i)
                r = .Range("a" & .Rows.Count).End(xlUp).Row
                With .Range("a1").Resize(r, ResizeCol)
                    For j = LBound(v) To UBound(v)
                        .Columns(6).Replace v(j), k(j), 1
                    Next
                    .Sort .Cells(2, 2), 1, , .Cells(2, 3), 1, .Cells(2, 6), 2, Header:=1
                    On Error Resume Next
                    .Columns(4).SpecialCells(4) = ReplaceV
                    .Columns(4).Replace "sector-wide", "Sector Wide", 1
                    .Columns(4).Replace "Sector Wide", vbNullString, 1
                    .Columns(4).SpecialCells(4).EntireRow.Delete
                    On Error GoTo 0
                    For j = LBound(v) To UBound(v)
                        .Columns(6).Replace k(j), v(j), 1
                    Next
                    .Columns(4).Replace ReplaceV, vbNullString, 1
                End With
            End With
        Next
    End With
    Application.ScreenUpdating = 1
                    
End Sub