strange Paste special

[pmarques]

I have a columm B with data outputed by a function
i copy them, and do Paste special + values + skip blanks
to another columm A with data, but doesn't skip blanks

What i realize is that the data aparently in blank is not.
Why? if i made paste special + (only) values

Any ideas?

Note: Attached an example

Thank

+-------------------------------------------------------------------
|Filename: example.zip
|Download: http://www.excelforum.com/attachment.php?postid=4331
+-------------------------------------------------------------------

 

[Gary L Brown]

The function was probably something like IF(A1=B1,"",0). This function will
create cells that LOOK LIKE THEY ARE BLANK but Excel does not see them that
way and so Paste Special copies these "" cells.
The macro below will delete those type of cells.

'/===========================================================/
Public Sub ClearBlankTextCells()
'this macro is designed to clear cells that were created
' using a formula something like if(A1=B1,"",0) that is then
' Paste/Value'd to create a cell with ' in it
'this macro does not affect formulas
Dim rng As Range, rngCell As Range

On Error GoTo err_Sub

Application.EnableCancelKey = xlErrorHandler

Set rng = Application.InputBox( _
Prompt:="Select Range to be Cleared: " & _
vbCr & vbCr & _
"Only ranges in CURRENT WORKSHEET may be selected" & _
vbCr & _
"Clear 'Blank' Text Cells so that formulas will not " & _
"return #VALUE!...", Title:="Range Selection...", _
Default:=Application.Selection.Address, Type:=8)

If Len(rng.Address) = 0 Then
MsgBox "No Cells were selected." & vbLf & vbLf & _
"Process Halted.....", _
vbExclamation + vbOKOnly, "WARNING....."
Exit Sub
Else
rng.Select
End If

For Each rngCell In Selection
If TypeName(Application.Intersect(rngCell, _
(ActiveSheet.UsedRange))) = "Nothing" Then
Exit For
End If
If rngCell.HasFormula = False And _
Len(rngCell.value) = 0 Then
rngCell.ClearContents
End If
Next rngCell

exit_Sub:
On Error Resume Next
Set rng = Nothing
MsgBox "Done..."
Exit Sub

err_Sub:
If Err.Number = 18 Then
If MsgBox("You have stopped the process." & vbCr & vbCr & _
"QUIT now?", vbCritical + vbYesNo + vbDefaultButton1, _
"User Interrupt Occured...") = vbNo Then
Resume 'continue on from where error occured
End If
End If

GoTo exit_Sub

End Sub
'/===========================================================/


HTH,