Solved: Sorting Cells with Empty Strings

[ajrob]

Here's potentially a problem caused by bad programming on my part, but am hoping for a work-around...

For years, I've used an empty string in an If-Then function to return a blank value. For example:
=IF(AND(AL200>0,AT200>0),AL200+AT200,"")

Now, picture a worksheet that's 250 rows long, or so, and 150 columns wide. And, there are five worksheets just like it.

I wrote a macro that selects this same column of data from each worksheet, and pastes it into a separate worksheet with the cumulative results. It sorts the data into descending order, and all of the empty string cells ("") get placed at the top.

Range("AL4:AL500").Select
Selection.Copy
Worksheets("Pkg Price History").Activate
Range("C6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Range("C6:C500").Select
Selection.Sort Key1:=Range("C6"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Here's my question...
First of all, is there a better way to return a blank value as opposed to an empty string? Second, if I use the empty string, is there a way to delete the cell contents of "", or a way to fool the sort function into thinking the cell is in fact blank?

Thanks

[rbrhodes]

1) I'm not having the blanks problem

2) In VBA it would be vbNullString

3) An example would be nice.

4) Select/Selection pairs are the first to go


[VBA]Sub stuff()

Range("A1:A500").Copy

With Sheets("Pkg Price History")
.Range("C1").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=True, _
Transpose:=False
.Activate
End With

Range("C1:C500").Sort Key1:=Range("C6"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub
[/VBA]

[ajrob]

Thanks for the tips. You may not like the coding (there are still a few Select/Selection pairs), but it works!

Dim iRow As Integer

Worksheets("DataXfr").Activate
Selection.AutoFilter Field:=17, Criteria1:="TBM"
Range("AV4:AV500").Copy
Worksheets("Pkg Price History").Activate
Range("E6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

iRow = 6
Do Until iRow = 500
If Cells(iRow, 5) = vbNullString Then
Cells(iRow, 5).Select
Selection.ClearContents
End If
iRow = iRow + 1
Loop

Range("E6:E500").Select
Selection.Sort Key1:=Range("E6"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("E6").Select