Results 1 to 7 of 7

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

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0

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

    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.

  2. #2
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    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.

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by alansidman View Post
    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.
    Attached Files Attached Files

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    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.
    Attached Files Attached Files

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Sorry for the late reply.

    replace this line

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

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

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Junior Member
    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    It works. Brilliant! Thank you

Similar Threads

  1. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  2. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  3. Delete Remove Rows By Criteria VBA Excel
    By marreco in forum Excel Help
    Replies: 5
    Last Post: 12-20-2012, 05:56 PM
  4. Replies: 2
    Last Post: 06-14-2012, 04:10 AM
  5. Replies: 7
    Last Post: 03-06-2012, 07:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •