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?