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?