Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
strange Paste special
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
strange Paste special
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Paste Special and Paste Object | Excel Discussion (Misc queries) | |||
paste special | values should work with merged cells | Excel Discussion (Misc queries) | |||
"paste special" "paste link" | Excel Discussion (Misc queries) | |||
Paste Special | Excel Discussion (Misc queries) |