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,
--
Gary Brown
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
"pmarques" wrote:
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
Thanks
+-------------------------------------------------------------------+
|Filename: example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4331 |
+-------------------------------------------------------------------+
--
pmarques
------------------------------------------------------------------------
pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110
View this thread: http://www.excelforum.com/showthread...hreadid=510018