Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sort a range based on 6th column in the range of Excel workseet

Posted on 2011-02-25
6
Medium Priority
?
335 Views
Last Modified: 2012-05-11
I have ranged names that go from column A to Column E.  However, after updating data via a form, I need to sort the data, but it is based on the data in column F.  How do I tell it to sort on Column F in the range?
With Range("ss_ACTIVEPORTFOLIOS")
            .Rows(.Rows.Count + 1).EntireRow.Insert
         With .Rows(.Rows.Count + 1)
                .Cells(1, 1).Value = Me.txtAddedBy.Value          'You can now refer to cells in the new row as .Cells(1, 1), .Cells(1, 2), etc.
                .Cells(1, 2).Value = Me.txtDateAdded.Value
                .Cells(1, 3).Value = "Yes"
                .Cells(1, 6).Value = Me.txtPortfolioCode.Value
                .Cells(1, 7).Value = Me.txtPortfolioName.Value
                .Cells(1, 8).Value = Me.txtURLSectorSummary.Value
                .Cells(1, 9).Value = Me.txtURLSectorDetail.Value
                .Cells(1, 10).Value = Me.txtURLRatingSummary.Value
                .Cells(1, 11).Value = Me.txtURLRatingDetail.Value
         End With
    End With
    CallRangeResets 'Resets all the ranges to include the new row
    ''NEED NEW ROW SORT HERE

Open in new window

0
Comment
Question by:ssmith94015
  • 3
  • 3
6 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 34983729
Sample Code. Please amend it for realistic situations.

Range("A1:G1000").Sort Key1:=.Range("F2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers

Open in new window


Sid
0
 

Author Comment

by:ssmith94015
ID: 34983786
actually, I got that far, problem is for each range, how to tell it to sort on that column.  I have gotten as far as the attached, but it still does not sort.
.Sort Key1:=Range("ss_ACTIVEPORTFOLIOS").Cells(1, 6), Order1:=xlAscending, Header:=xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34983802
Which Range does Range("ss_ACTIVEPORTFOLIOS") refer to?

Sid
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ssmith94015
ID: 34983927
Sid, actually, I solved it.  I found first that I was putting the code in the wrong place.  I really belongs in the module that dynamically resets the ranges.  I have attached what works.
Public Sub DynamicRangeSettingsBondProfile()
'THIS IS FOR THE Settings WORKSHEET!!!  It re-sets the entire range for the profile that will be
'populated from the frmAddProfile form as the data has been entered or after any profiles
'have been closed - closed by the frmCloseProfile form
On Error GoTo ErrorHandler
'If user adds a new Bond profile, this dynamically determines what the
'new ranges will be.  Need to lock the column headers just to be sure.
Dim strRange            As Range
Dim strAddress          As String
Dim varName             As Variant
Dim i                   As Integer
Dim intEnd              As Integer
Dim intRowNum           As Integer
Dim intAddressNum       As Integer
Dim rngVarName          As Range

'First, find where the data source area starts
With ThisWorkbook.Worksheets("Settings").Range("A:A")
    For Each varName In Array("ss_ACTIVEPORTFOLIOS", "ss_ACTIVEMISC", "ss_ACTIVEPORTFOLIOMASTERSTATS", _
                               "ss_INDEXSTANDARDPORTFOLIOS", "ss_INDEXMISCELPORTFOLIOS", "ss_INDEXSUPERFUNDPORTFOLIOS", _
                                "ss_INDEXPORTFOLIOMASTERSTATS")
    Set strRange = .Find(varName, LookIn:=xlValues)
    strAddress = strRange.Address
'Initialize loop counter to seed its value
    i = 1
        'Loop through the fields until a blank row is hit, this is the end of the range
        Do Until .Range(strAddress).Offset(i, 0) = ""
            i = i + 1
        Loop
            intRowNum = .Range(strAddress).Row 'Row for varName address
            intAddressNum = intRowNum + 2 'Start range below headers for group
            intEnd = (intRowNum + i) - 1  'Last row for end of range
            .Range("A" & intAddressNum & ":K" & intEnd).Name = varName 'Range name in workbook
            
With ThisWorkbook.Worksheets("Settings").Range(strRange)
    .Select
    .Sort Key1:=Range(strRange).Cells(1, 6), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
 End With
            
    Next
   
    
End With


Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " Description: " & Err.Description
    Resume Exit_ErrorHandler

End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34983959
Great :)

Sid
0
 

Author Closing Comment

by:ssmith94015
ID: 34984035
This was the path I started with and led me to eventually solve it issue.  Thank you.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question