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

[valesilver]

Hi. I am very new to VBA and need some help on how to sort my data by multiple columns in several sheets and then delete some specific rows.

All my sheets have the same number of columns (from A to AI), but different number of rows (these could be e.g. 900 or 5900). My data has headers. The number of sheets I need to work on can vary year on year, but usually is more than 50.

Firstly I need to sort columns B and column C by values A to Z. I also need to sort column F by a custom list, whose list entries are "% Agree" and "Mean score". There are other values included under the column F but these don't need to be sorted. I basically need to have "% agree" and "mean score" first.

I then need to apply this sorting to all my sheets within my workbook.

Secondly, I need to delete those rows that have the following values in column D: "sector-wide" or "Sector Wide" in every sheet.

Hope you can help with this. Thank you very much.

[alansidman]

Suggest you post (upload) a sample workbook so that precise code can be written for you. Click on Go Advanced Button when you reply and follow the wizard.

[valesilver]

Suggest you post (upload) a sample workbook so that precise code can be written for you. Click on Go Advanced Button when you reply and follow the wizard.

Thank you for your reply. Please find attached a sample workbook. This only includes 3 sheets (out of 50 or more) with fewer rows than the real ones.

[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.

[Admin]

Hi

Sorry for the late reply.

replace this line

.Sort .Cells(2, 2), 1, , .Cells(2, 3), 1, .Cells(2, 6), 2, Header:=1

with

.Sort .Cells(2, 2), 1, , .Cells(2, 3), 1, .Cells(2, 6), 1, Header:=1

[valesilver]

It works. Brilliant! Thank you