Option Button/Sorting

[erutherford]

The project has grown and has a few pains still and this is one. Sometimes it will work and sometimes it doesn't and not sure why. The objective is to sort on Col "J " within a range. Seems simple. We never allow more than 50 entries, so I first setup the range @ "A2:A50", but if there were blank cells then an error would occur "sort reference is not valid". Then I would manual change it the last full cell, then it worked. Save the file and next time I would use the function, like this morning an "error".

Any thoughts or a better way?

<code>
Private Sub CommandButton2_Click()
If OBSummary.Value = True Then
Unload UserForm7
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then ThisWorkbook.Sheets("Summary").Range("PrtSummary").PrintPreview

'*****************************************************
ElseIf OBDetailSum.Value = True Then
Unload UserForm7
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then ThisWorkbook.Sheets("SummaryDetail").Range("PrtSummaryDetail").PrintPreview

'*****************************************************
Else
Unload UserForm7

If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
ThisWorkbook.Sheets("Entries").Range("A2:J43").Sort Key1:=Range("J3"), Order1:=xlAscending, Header:=xlYes
ThisWorkbook.Sheets("Entries").Range("A2:J43").Sort Key1:=Range("I3"), Order1:=xlAscending, Header:=xlYes
Range("A2:J43").PrintPreview
PrToFileName = True

ThisWorkbook.Sheets("Entries").Range("A2:J43").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End If
End If
End Sub
</code>

 

[erutherford]

Thanks as always!

 

[Fluff]

You're welcome