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?