Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sorting with Zero or blank cells

Posted on 2016-08-13
11
Medium Priority
?
37 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 49

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 49

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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 49

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

618 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