Sorting a Dynamic Range with Multiple Variables

[reberryjr]

I've reviewed this forum and 2 others, but have been unable to find answers that fit my scenario. I'm needing to sort a range of rows that changes. The sort will occur on 2 columns. I've recorded the macro, but it continues to specify a set range. I was able to find the snippet included in the code below, but I am getting an error saying that a variable isn't set. The code works fine without the sorting, so I know that's the issue, I just can't nail it down. Any help is appreciated.

Private Sub UserForm_Initialize()

Dim cGender As Range
Dim cPymtFreq As Range
Dim cEntryType As Range
Dim cStatus As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim LastRow As Range


Set ws1 = ThisWorkbook.Sheets("Bios")
Set ws2 = ThisWorkbook.Sheets("Stats")
Set ws3 = ThisWorkbook.Sheets("Services")
Set ws4 = ThisWorkbook.Sheets("Payments")
Set ws5 = ThisWorkbook.Sheets("Variables")


LastRow = ws1.Range("G" & Rows.Count).End(xlUp).Row + 1


For Each cGender In ws5.Range("Gender")
    With Me.cobo_Gender
        .AddItem cGender.Value
    End With
Next cGender


For Each cPymtFreq In ws5.Range("PymtFreq")
    With Me.cobo_DPFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


For Each cPymtFreq In ws5.Range("PymtFreq")
    With Me.cobo_DCFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


For Each cPymtFreq In ws5.Range("PymtFreq")
    With Me.cobo_OCFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


For Each cPymtFreq In ws5.Range("PymtFreq")
    With Me.cobo_CTIFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


For Each cPymtFreq In ws5.Range("PymtFreq")
    With Me.cobo_CTOFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


ws1.Range("A2:M" & LastRow).Select
ActiveWorkbook.Worksheets("Bios").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Bios").Sort.SortFields.Add Key:=Range( _
"G2:G" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Bios").Sort.SortFields.Add Key:=Range( _
"B2:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
    .SetRange Range("A2:M" & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


End Sub

 

[MARK858]

Does changing

.SetRange Range("A2:M" & LastRow)

to

.SetRange Parent.Range("A2:M" & LastRow)

make any difference?

 

[reberryjr]

Unfortunately, it did not.

 

[MARK858]

What line is highlighted when the error occurs?
and what happens with

ws1.Sort.SortFields.Clear
ws1.Sort.SortFields.Add Key:=ws1.Range( _
"G2:G" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ws1.Sort.SortFields.Add Key:=ws1.Range( _
"B2:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

 

[reberryjr]

There isn't a line that gets highlighted. An error window pops up, without an option to debug. If I step through the code, the UserForm appears and I get the error window.

 

[MARK858]

Doh, to start with I'm not looking in the right place.

Try changing

Dim LastRow As Range

to

Dim LastRow As Long

 

[reberryjr]

I would like to second and if possible, third that DOH! Can't believe I did that! Thanks for the second pair of eyes!

 

[MARK858]

You're welcome (and I must learn to look for the simpler issue first )