You can see what's left over if you toggle a setting:
tools|options|transition tab|check Transition navigation keys

You'll see a single apostrophe in those cells.

Remember to toggle that setting back to off.

One way to clean up that mess left over from edit|copy, edit|paste
special|values:

select all those cells
edit|replace
what: (Leave blank)
with: $$$$$
replace all

Then do it again
edit|replace
what: $$$$$
with: (leave blank)
replace all



Dennis wrote:

For others who may read this thread.

If you Copy/Paste's (values-only), when the source range contains formulas
that set the cell(s) value(s) to "" (like =IF(ISBLANK(B2),"",B2), the
Pasted-to cell range can become part of the used-range (at least as it
relates to functions using .End(xlUp or xlDown or xlRight or xlLeft).

The receiving cells (of the combined copy/paste procedure) do not contain
any visible values or formulas, but to XL the cells are not the same as
never-used cells.

**********************************************

"Dave Peterson" wrote:

The cells aren't empty. They contain formulas that evaluate to "".

So that .specialcells() stuff won't help.

I would think the quickest solution would be just to look down column F looking
for "". As soon as you find it, go back up a row and do the copy.

This may work if your range to copy is contiguous.

Option Explicit
Sub testm()

Dim TopCell As Range
Dim BotCell As Range
Dim myRngToCopy As Range

With Worksheets("Pivot table")
Set myRngToCopy = Nothing
Set TopCell = .Range("F5")
Set BotCell = TopCell
If TopCell.Value = "" Then
'do nothing
Else
Do
If BotCell.Offset(1, 0).Value = "" Then
Exit Do
Else
Set BotCell = BotCell.Offset(1, 0)
End If
Loop
Set myRngToCopy = .Range(TopCell, BotCell).Resize(, 2) '2 columns
End If

If myRngToCopy Is Nothing Then
'nice msgbox
Else
myRngToCopy.Copy
Worksheets("mysheet").Range("b239").PasteSpecial
Paste:=xlPasteValues
End If
End With

End Sub




Dennis wrote:

Thanks Dave!

I inserted your code into my macro as follows.

With Worksheets("Pivot Table")
Set MyRngToCopy = Nothing
On Error Resume Next
Set MyRngToCopy = .Range("F5:G" & .Cells (.Rows.Count, "G")
.End(xlUp).Row).Cells.SpecialCells(xlCellTypeFormu las)
On Error GoTo 0
If MyRngToCopy Is Nothing Then
MsgBox "Nothing to Copy"
Else
Sheets(MySheet).Select '?? Could this confuse the "Selection"
Range("B239").Select '?? Could this confuse the "Selection"
MyRngToCopy.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End If
End With

The copy process still inserts "phantom" cells. The cells to copy from are
formula cells - just with no value. The formula in the cells being copied
from is: =IF(ISBLANK(B2),"",B2)

On the receiving sheet, XL does not see formulas or blanks (in the subject
area) but does see constants (using the GoTo routine).

Note above my comments about the the prior "selections." to the
MyRngToCopy.Copy
process. BTW, I moved those other selections out and in the "With" process
with the same result. Do we need possibly ....
SpecialCells(xlCellTypeFormulas, XX)?

Dennis

"Dave Peterson" wrote:

If you try this by hand, does it select the cells that you want copied? And if
it does, is that range a nice rectangular contiguous block of cells?

Select F5:Gxxx
edit|goto|special
formulas
click ok.

If it does, you could do that same kind of thing in code.

Dim myRngToCopy As Range

With Worksheets("pivot table")
Set myRngToCopy = Nothing
On Error Resume Next
Set myRngToCopy = .Range("f5:G" & .Cells(.Rows.Count, "G").End(xlUp).Row) _
.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRngToCopy Is Nothing Then
MsgBox "nothing to copy"
Else
myRngToCopy.Copy
'later...
Selection.PasteSpecial Paste:=xlPasteValues
End If

=========
Did you really mean you had formulas in that F5:Gxx range???

Dennis wrote:

Using 2003

Sheet1: Current macro calculates a range of cells from a fixed upper-left
range cells F5 to G200.: (Note that Sheet1 is a Pivot-table already using
Auto filter.)

Sheets("Pivot Table").Range(Cells(5, "F").Address, Cells _
(Rows.Count, "G").End(xlUp).Address).Copy

(followed by:)

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=True, Transpose:=False

I would prefer that the copy command would evaluate only the cells whose
formula calculates a value. By this I mean: Many of the cells in the F5 to
G200 range do not have a calculated value. Assuming that only 5 rows have
values, the copy command will copy the calculated range F5 to G200, meaning
that 195 two-column cell combinations will be pasted to the receiving range
wasting space on Sheet2.

My macros then copy another range from Sheet3 beginning at row 201. (Again,
there are 195 essentially wasted rows between the cells pasted from Sheet1)

I have attempted to Copy using VisibleCells and/or Paste-special SkipBlanks
= True - yet I still get the 195 "Wasted-space" cells.

It seems that there are three solutions:
One is a smarter way to copy only the cells that the
underlying formula calculates a value.

-or-

Another, is to Paste only those cells from Sheet1 which
have values (text or numeric)

-or-

Develope a routine to delete the empty rows on Sheet2.

Which of the three is the smartest/most efficient?

I need help with coding the syntax in VBA.

Thanks, Dennis

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson