Solved

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

Posted on 2011-02-25
6
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 

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

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.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

691 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