Solved

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

Posted on 2011-02-25
6
316 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 500 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

830 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