Excel: Sort Blank Cells First 2

[joebb3]

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.

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

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

Thanx in Advance!
Joe

 

[SkipVought]

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,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[joebb3]

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]

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

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

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.


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

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

 

[DaveInIowa]

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]

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,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[joebb3]

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]

No problem. ;-)

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[joebb3]

For posterity, here is what I used.

'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]

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



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}