Sorting with Zero or blank cells

Hello EE,

Can someone please help me modify this code to sort my data while ignoring cells with blank values in the "M" column?  I also need to incorporate the code with another procedure. Here is my macro:

Option Explicit

Sub Sort_Descending_With_Header()

Range("I15:M31").Sort _
Key1:=Range("M15"), Order1:=xlDescending, Header:=xlYes

End Sub

Open in new window

Thanks in advance with your help
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please show or provide an example of what the output should look like given input with blanks.
fb1990Author Commented:
Thank Martin,

Here is my example data.  When sorted CUST H should be in the bottom

Cust	Goals1	Goals2	Goals3	Goals4
a	0%	59%	43%	34%
b	100%	27%	55%	61%
c	0%	100%	100%	67%
d	0%	38%	55%	31%
e	18%	4%	15%	100%
f	100%	100%	100%	100%
g	0%	100%	100%	67%
h	100%	50%	42%	
i	0%	100%	100%	67%
j	0%	59%	43%	34%
k	0%	8%	8%	67%

Open in new window

Martin LissOlder than dirtCommented:
Is it column M that contains the blank?
Do you always want to sort the range I15:M31?
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

fb1990Author Commented:
Yes it is column M.  Great question on the other part.  The data can grow to more than M31 to M50 maybe.
fb1990Author Commented:
i know i am asking for too much, but i want to call this sort query from another procedure

Martin LissOlder than dirtCommented:
This assumes that the first column to be included in the sort is column "I". It also assume the first data row is 15 but that can be changed via the FIRST_DATA_ROW constant. Put the sub in a code module and the sub can be called from anywhere.
Public Sub Sort_Descending_With_Header()
Dim lngLastRow As Long
Dim lngRow As Long

Application.ScreenUpdating = False
lngLastRow = Range("I1048576").End(xlUp).Row

For lngRow = FIRST_DATA_ROW To lngLastRow
    If Cells(lngRow, "M") = "" Then
        Cells(lngRow, "M") = "-999999"
    End If

Range("I" & FIRST_DATA_ROW & ":M" & lngLastRow).Sort _
Key1:=Range("M" & FIRST_DATA_ROW), Order1:=xlDescending, Header:=xlYes

For lngRow = FIRST_DATA_ROW To lngLastRow
    If Cells(lngRow, "M") = -999999 Then
        Cells(lngRow, "M") = ""
    End If

Application.ScreenUpdating = True
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fb1990Author Commented:
Hello Martin,

It sorted the data, but it is not sorting in column descending when there is no blank in the data.  I forgot to mention that there will be time when there is no blanks in the data.  Can you help me modify the code?  The data needs to be sorted descending with or without blanks.  My apologies for the confusion
Martin LissOlder than dirtCommented:
Please try it again because my test worked. Here's a picture.
fb1990Author Commented:
Thanks, i just tried it and it worked.

Thank you
fb1990Author Commented:
Thank you very much!
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.