Solved

Sorting with Zero or blank cells

Posted on 2016-08-13
11
33 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 47

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 47

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 47

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 47

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 47

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

756 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