Sort with a "" formula result

[SteveM]

In this example assume:
A1 never had a value and is blank
Cell B1 contains the formula =if(a1=0,"",a1) and should result in ""

Rows D and E are as follows

Row num Value
1 widgit
2
3

If I manually Copy B1 and Paste values in E3(below Widget), then sort by row
E with header, I come up with the following result:

Row num Value
2
1 widgit
3


B1 should have a result of "", which I would think is the same value as a
blank cell, hoever the sort does not group these together (row 2 and 3).
Sort also puts the result of row 2 "" ahead of widget in this example.

This problem was identified in a much more complex and larger project. The
above example is simplified for explantion purposes. In the real project the
values from column E are put into a combo box after a sort, and all the
blanks come to the top. We could sort z to a , but most users expect a to z
sorts as would I.

Does anyone know how to paste a value that will sort the same as a blank cell?

 

[Pete_UK]

If instead of returning a blank you returned a very "large" text
value, like "zzzzz", then this will be sorted to the bottom of the
list (well, above your true blanks, anyway). You can apply conditional
format to the cell such that if it contains "zzzzz" then colour it
white (so that it will appear to be blank), and if you are doing Paste
Special then click on Formats as well as Values to maintain this
colour.

Main drawback is if you are doing arithmetic on the cell or the range
- you will need to check for the cell possibly containing "zzzzz", but
then you might be checking for "" elsewhere anyway, so you can include
this check at the same time.

Hope this helps.

Pete

 

[Conan Kelly]

SteveM,

When you have an IF function in a cell that makes it appear blank, the value
of the cell ends up being a zero-length string. So when you paste as
values, you are pasting a zero-length string instead of a blank cell.

Maybe someone has a better way of doing this, but the only way around this
that I can think of is to clear the contents of all the cells in the current
selection after pasting. I use this code to do that:

Sub ClearBlankCells()
Dim prngCell As Range
Dim pintCalcMode As XlCalculation

pintCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

For Each prngCell In Selection
If prngCell = "" Then
prngCell.ClearContents
End If
Next prngCell
Application.Calculation = pintCalcMode
End Sub


HTH,

Conan

 

[Conan Kelly]

sorry!!!

Maybe someone has a better way of doing this, but the only way around this
that I can think of is to clear the contents of all the cells in the
current selection after pasting. I use this code to do that:

Should read:

Maybe someone has a better way of doing this, but the only way around this
that I can think of is to clear the contents of all the cells in the
current selection THAT APPEAR BLANK after pasting. I use this code to do
that:

sorry for leaving that out.

 

[SteveM]

Conan, I think your idea should work , but I am getting a PasteSpecial method
of class failed error with the following code. It worked prior to adding the
lines you specified.

Range("Data_to_Save").Select
Selection.Copy
Sheets("Client Data").Activate

pintCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

For Each prngCell In Selection
If prngCell = "" Then
prngCell.ClearContents
End If
Next prngCell
Application.Calculation = pintCalcMode

Range("c2").Select
ActiveCell.Offset(rowOffset:=Range("Index_Num")).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=True

 

[SteveM]

It appears to be working with the following:

Sheets("Client Info").Select
Range("Data_to_Save").Select

pintCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

For Each prngCell In Selection
If prngCell = "" Then
prngCell.ClearContents
End If
Next prngCell
Application.Calculation = pintCalcMode

Selection.Copy
Sheets("Client Data").Activate
Range("c2").Select
ActiveCell.Offset(rowOffset:=Range("Index_Num")).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Thanks for your help

 

[Conan Kelly]

SteveM,

It looks like you are running my code on the original data. Doing that
would probably delete the formulas that are causing the cell to appear
blank.......turning them into actual blank cells..........and then
copying-pasting as values afterwards.

I was thinking more like copying, pasting as values, then running the code
to loop through each of the "pasted as values" cells. That will keep your
IF() functions in tact.

After pasting as values, the pasted data should be selected, so there should
be no need to figure out what data needs to be selected and reselecting it.
Just run the "loop through each cell" code to clear the contents of the
cells that appear blank.

Something like this:

Sheets("Client Info").Select
Range("Data_to_Save").Select
Selection.Copy
Sheets("Client Data").Activate
Range("c2").Select
ActiveCell.Offset(rowOffset:=Range("Index_Num")).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

pintCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

For Each prngCell In Selection
If prngCell = "" Then
prngCell.ClearContents
End If
Next prngCell
Application.Calculation = pintCalcMode



HTH,

Conan

 

[SteveM]

Thanks Conan, The range I copied from all receive controlsource data, so
there are no formulas, but I think the way you have it here is cleaner. One
final question, I tried to modefy the line

(If prngCell = "" Then) to (If prngCell = "" or 0 Then)

but it did not clear the 0's. I also tried "0" without success.

Any thoughts.

Steve

 

[Conan Kelly]

SteveM,

Try this:

If prngCell = "" OR prngCell = 0 Then

In an if statment (or any other statement), you can not compare one item to
2 different values. You can only compare one item to one value. If you
need to compare one item to 2 different values, you need to compare it to
those values separately......you need the item listed twice with a
comparison to each instance of it.

HTH,

Conan