Solved

Sorting with Zero or blank cells

Posted on 2016-08-13
11
32 Views
Last Modified: 2016-08-13
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
0
Comment
Question by:fb1990
  • 6
  • 5
11 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41754882
Please show or provide an example of what the output should look like given input with blanks.
0
 
LVL 1

Author Comment

by:fb1990
ID: 41754890
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

0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41754898
Is it column M that contains the blank?
Do you always want to sort the range I15:M31?
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 1

Author Comment

by:fb1990
ID: 41754903
Yes it is column M.  Great question on the other part.  The data can grow to more than M31 to M50 maybe.
0
 
LVL 1

Author Comment

by:fb1990
ID: 41754905
i know i am asking for too much, but i want to call this sort query from another procedure

Thanks...
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41754986
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
Const FIRST_DATA_ROW = 15

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
Next

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
Next

Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:fb1990
ID: 41755014
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
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41755048
Please try it again because my test worked. Here's a picture.
test
0
 
LVL 1

Author Comment

by:fb1990
ID: 41755058
Thanks, i just tried it and it worked.

Thank you
0
 
LVL 1

Author Closing Comment

by:fb1990
ID: 41755059
Thank you very much!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41755062
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

790 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