Sort data in excel spreadsheet

How can I sort data in Excel spreadsheet through VB 6.0. I have the following code but it fails to sort the data.

optplot.Range(Cells(2, 8), Cells(Val(Me.Text1.Text) + 1, 10)).Select
optplot.Selection.Sort Key1:=optplot.Cells(2, 10), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
oXL.DisplayAlerts = False

Than you for your help in advance.
Who is Participating?
Chris BottomleyConnect With a Mentor Commented:

As a bit of a newcomer myself I am not certain, but inserting a string for a cell address to replace text1.text fails for me so I assume it represents your problem.

Removing the superfluous optplot from the second line, (Selection.Sort Key1:=optplot.Cells(2, 10), Order1:=xlAscending, Header:=xlGuess etc.) results in a good sort so is this the problem?
Do you get an error of some kind?
Chris BottomleyCommented:
Looks like I cut the wrong bit!!!

To be clear I suspect the line should be :

Selection.Sort Key1:=optplot.Cells(2, 10), Order1:=xlAscending, Header:=xlGuess etc.
Try this:

With optplot
    .Range(.Cells(2, 8), .Cells(Val(Me.Text1.Text) + 1, 10)).Select
    .Selection.Sort Key1:=.Cells(2, 10), Order1:=xlAscending, OrderCustom:=1
End With
oXL.DisplayAlerts = False

But in general, we need to see more of your code. You may not be connecting the Excel sheet at all.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.