Sort with a "" formula result
 

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?

Sort with a "" formula result
 

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

On Nov 26, 11:48 pm, SteveM wrote:
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?

Sort with a "" formula result
 

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




"SteveM" wrote in message
...
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?

Sort with a "" formula result
 

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.






"Conan Kelly" wrote in message
...
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




"SteveM" wrote in message
...
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?

Sort with a "" formula result
 

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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=True


"Conan Kelly" wrote:

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




"SteveM" wrote in message
...
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?

Sort with a "" formula result
 

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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Thanks for your help

"SteveM" wrote:

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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=True


"Conan Kelly" wrote:

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




"SteveM" wrote in message
...
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?

Sort with a "" formula result
 

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")).A ctivate
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" wrote in message
...
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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Thanks for your help

"SteveM" wrote:

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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=True


"Conan Kelly" wrote:

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




"SteveM" wrote in message
...
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?

Sort with a "" formula result
 

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" wrote:

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")).A ctivate
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" wrote in message
...
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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Thanks for your help

"SteveM" wrote:

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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=True


"Conan Kelly" wrote:

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




"SteveM" wrote in message
...
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?

Sort with a "" formula result
 

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




"SteveM" wrote in message
...
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" wrote:

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")).A ctivate
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" wrote in message
...
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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Thanks for your help

"SteveM" wrote:

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")).A ctivate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=True


"Conan Kelly" wrote:

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




"SteveM" wrote in message
...
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?