Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-25
6
Medium Priority
?
330 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 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

610 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