Sort Specific Range VBA

0
votes

I'm trying to sort a range based on a specific criteria when the user select check boxes from a user form in Excel.

The user is given the option of 4 check boxes (each check box will sort a specific range). I've tried a few methods and can't seem to figure it out, I keep getting "Object Required" on run-time error

Private Sub btnConfirm_Click()

    If (chkbxValid.value) Then
        SortData (Range("H4:I1000"))
    End If
    If (chkbxValidDuplicate.value) Then
        SortData (Range("K4:L1000"))
    End If
    If (chkbxInvalid.value) Then
        SortData (Range("N4:O1000"))
    End If
    If (chkbxInvalidDuplicate.value) Then
        SortData (Range("Q4:R1000"))
    End If

End Sub

And then here's the second segment of code

Private Function SortData(rng As Range)

    FR = 1
    FC = 1
    LR = 1000
    LC = 2
    SC = 2

    Sheets("TRACKER_2.0").rng(cells(FR, FC), cells(LR, LC)).Sort Key1:=Range(cells(FR, SC), cells(LR, SC)), Order1:=xlAscending

End Function
1
vbaexcel
You're trying to use more than 1 column as the key. – David Manheim
Drop the brackets around your function calls. Also, Key1 must specify the first sort field, you are using the whole range so that may have unintended consequences. – SJR

1 Answers

1
votes

For sorting, there are more criteria:

fr = 1 'first row
fc = 1 'first column
lr = 200 'last row
lc = 10 'last column
sc = 4 'sort column
Range(Cells(fr,fc),Cells(lr,lc)).Sort key1:=Range(Cells(fr,sc),CellS(lr,sc)), order1:=xlAscending, Header:=xlNo

Using Range("A1") style format:

lr = 200 'last row
Range("A1:J" & lr).Sort key1:=Range("D1:D" & lr), order1:=xlAscending, Header:=xlNo