Solved

Sorting with Zero or blank cells

Posted on 2016-08-13
11
28 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 45

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 45

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

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 45

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 45

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now