ajrob
01-12-2009, 11:05 PM
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
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