Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

joebb3 (Programmer)
11 Feb 09 15:32
I appologize if this has been asked and answered, I searched the threads and FAQ but couldn't find the answer.

I have a spreadsheet with a 2 row header field that I want to sort by a "Date Complete" column from VBA.

That's easy enough...  But I want all the BLANK cells (Incomplete Items) at the top.

Here is my sort code that works just fine other than the fact that the blanks are at the bottom.

CODE

ActiveSheet.Range("A3").Sort Key1:=ActiveSheet.Columns("F"), Header:=xlGuess

"F" is the column that contains "Date Complete"

Thanx in Advance!
Joe
 
Helpful Member!  SkipVought (Programmer)
11 Feb 09 16:24



Hi,

make a helper column that has 0 for blank and 1 for non blank dates.

Sort using that column as primary then the date column as secondary.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

joebb3 (Programmer)
12 Feb 09 9:59
Skip,

Thanx for the suggestion, and I do appreciate your time, but there has got to be a way to do it without adding unnescesary data to the spreadsheet.  

And if anyone knows that it ISN'T possible without adding the helper column like Skip suggests, please indicate that as well so I know, and can add the column.

Thanx again!
Joe
SkipVought (Programmer)
12 Feb 09 10:01



Sure, you can write your own sorting algorythm.  Knock yourself out!

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

AnotherHiggins (TechnicalUser)
12 Feb 09 10:26
Joe - Skip has probably forgotten more about Excel than most of us will ever know, but there's no way that you'd know that, so I'm posting to assure you that Excel does not do what you want using built-in functionality.

You can add the helper column, then sort, then delete the helper column all within the macro. If you wrap the code in Application.ScreenUpdating = False, then users won't see the magic happening behind the scenes.
 

-John
    The plural of anecdote is not data

Help us help you. Please read FAQ 181-2886 before posting.

Helpful Member!  DaveInIowa (Programmer)
12 Feb 09 13:23
You could replace all blank cells with a special date value, say 1/1/1900, sort your sheet, then do another global replace changing 1/1/1900 back into blank entries.
SkipVought (Programmer)
12 Feb 09 13:27


DaveInIowa make a very valid point.  Empty or invalid data values in Excel can cause some undesirable results, especially if you want to group Dates in a PivotTable, for instance.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

joebb3 (Programmer)
12 Feb 09 15:25
All,

As I searched the threads further, I realized that Skip was da man!  

I do have a spot in my code where I could populate a helper column prior to a sort.  I'm not sure if I'm going to use 0/1 or DaveInIowa's suggestion, but I do understand the pivot table caution.

Please know that I meant no disrespect to anyone and appreciate everyone's replies!

Joe
VB Hack
 
SkipVought (Programmer)
12 Feb 09 15:36


No problem.  winky smile

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

joebb3 (Programmer)
12 Feb 09 15:48
For posterity, here is what I used.

CODE

'Sub to find the last row - returns 'sheetlength'
Call FindLastRow

'Place temporary date
For x = 3 To sheetlength
    If Trim(ActiveSheet.Cells(x, 6) & " ") = "" Then ActiveSheet.Cells(x, 6) = "1/1/2099"
Next x

'Sort
ActiveSheet.Range("A3").Sort Key1:=ActiveSheet.Columns("F"), Order1:=xlDescending, Header:=xlGuess

'Remove temporary date
For x = 3 To sheetlength
Debug.Print ActiveSheet.Cells(x, 6).Value
    If ActiveSheet.Cells(x, 6).Value = "1/1/2099" Then ActiveSheet.Cells(x, 6) = ""
Next x

I used a future date so I could sort descending.  That way the most recently completed items are just under the blanks.

Thanx for all the help!
Joe
 
SkipVought (Programmer)
12 Feb 09 15:54



FYI,

"1/1/2099" is not a date. It is TEXT.  Dates are NUMBERS.

However, Excel is "helpful" at times and see's that you entered TEXT that looks like you wanted it to be a date.  So Excel CONVERTS it to a DateSerial value and DISPLAYS that NUMBER in a Date Format.

FAQ68-5827: Why do Dates and Times seem to be so much trouble?

 

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close