• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort Dynamic range - Help needed

SG

Member
Hi Experts,

I need a solution to sort the data on basis of location & highest marks. For this, i have created a dynamic named range to call it by macro & then sort. My problem comes when the range also selects the blank formulas in sheet which shouldn't be selected. Only visible data should selected in that range. Please help me with the solution.
 

Attachments

  • Dynamic Range.xlsm
    14.7 KB · Views: 8
Change Name Manager as:

=OFFSET(Sheet1!$A$1,0,0,MATCH("",Sheet1!$A$2:$A$20,0),COUNTA(Sheet1!$A$1:$D$1))
 
In addition to the above query, i'm unable to sort the data. Please have a look. the requirements are mentioned in the sheet. I have created the macro & attached the sheet. Please solve my query.
 

Attachments

  • vDynamic Range.xlsm
    19.1 KB · Views: 4
Hi SG,

Please note something first, and please note this is as far as my Excel knowledge is concern there may be something which I am missing.

1. You cannot sort two columns simultaneously, I mean how the location be A-Z and at the same time marks be highest to lowest. The highest scorer can be from a country starting with Z . Give a thought.

2. In your formula you are inserting a null string "" where there is blank in B column. Now A null string is always come first in comparison to any alphabets. SO that's why you are getting blanks cells first. I had modified your formula to insert a "Ω" (Greek Omega) in blank cells . This is treated as biggest alphabets (I hope so) so if now you sort your database as per location you will get the correct result.

See the attached file.

And please note this forum operates 24/7 hours, but all the members have many others jobs to be carried out. So,it might take some time to get a query solved. So try to keep some patience from next time.

Regards,
 

Attachments

  • vDynamic Range.xlsm
    20.2 KB · Views: 5
Hi Somendra,

Thanks for the reply, however my intention wasn't like somebody would reply to my query in a moment.
Thanks for your suggestion but my issue didn't get solved.I'll try otherways.
 
Hi, SG!

In general...

Next time please post here the issue description, keeping it into your workbook too if you consider it propertly.
This will help users who read this to decide whether they'd be able or want to analyze it further, without
having to download/open any file.

About this thread...
Try this code with the dynamic named range defined as Debraj(ex-Roy) wrote:
Code:
Option Explicit

Sub WhyDoingItInTheHardWay()
    ' constants
    Const ksWS = "Sheet1"
    Const ksRng = "myrange"
    ' declarations
    Dim rng As Range
    ' start
    Set rng = Worksheets(ksWS).Range(ksRng)
    ' process
    With rng.Parent
        With .sort
            With .SortFields
                .Clear
                .Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .Add Key:=Range("D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            End With
            .SetRange rng
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Just advise if any issue.

Regards!
 
Back
Top