Solved

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

Posted on 2011-02-25
6
312 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

776 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