Link to home
Start Free TrialLog in
Avatar of fb1990
fb1990

asked on

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Please show or provide an example of what the output should look like given input with blanks.
Avatar of fb1990
fb1990

ASKER

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

Is it column M that contains the blank?
Do you always want to sort the range I15:M31?
Avatar of fb1990

ASKER

Yes it is column M.  Great question on the other part.  The data can grow to more than M31 to M50 maybe.
Avatar of fb1990

ASKER

i know i am asking for too much, but i want to call this sort query from another procedure

Thanks...
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fb1990

ASKER

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
Please try it again because my test worked. Here's a picture.
User generated image
Avatar of fb1990

ASKER

Thanks, i just tried it and it worked.

Thank you
Avatar of fb1990

ASKER

Thank you very much!
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