Sort Dynamic range - Help needed

[SG]

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.

 

[Debraj]

Change Name Manager as:

=OFFSET(Sheet1!$A$1,0,0,MATCH("",Sheet1!$A$2:$A$20,0),COUNTA(Sheet1!$A$1:$D$1))

 

[Somendra Misra]

You can also try this:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$D$20,MATCH("",Sheet1!$A$2:$A$20,0)-1,)

Regards,

 

[SG]

Thanx alott.....Debraj.
Thanx somendra, i'll try your formula too.

 

[SG]

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.

 

[SG]

Hi Experts,

Please help..!!!

 

[Somendra Misra]

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,

 

[SG]

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.

 

[SirJB7]

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:

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!