Sort Data By Multiple Columns And Delete Some Rows Based On Criteria

[Admin]

Hi

Try this

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

[valesilver]

Great! Thank you. It seems to be working on my real workbook, except for the order of column F, as "% agree" and "mean scores" come at the end rather than on top of the range. It might be my fault as the sample workbook I included had "% agree" and "mean scores" values only for those rows I wanted to be deleted (with sector wide in column D). I attach an amended sample workbook for you to have a look. Thank you again.