Copy/Paste how to avoid the copy of formula cells w/o calc values

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

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

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(xlCellTypeFormulas)
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
>

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(xlCellTypeFormulas)
> 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, I did not realize that "" was actually entered into the cell further
assuming ( ha! ha! on me) "" was a code to XL to enter "nothing." Now, I
understand why blank cells are not necessarily blank even though I do not see
a value or "formula" in the formula bar.

Please check back tomorrow I am off to a meeting.

Thank you very much.


"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(xlCellTypeFormulas)
> > 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
>

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(xlCellTypeFormulas)
> > 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
>

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(xlCellTypeFormulas)
> > > 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

Dave,

Thanks again for your help and insight.

I made a change in the first lin of the "IF" Statement as testing for ""
stopped the calculated(ing) range at one row.

In the process,
I attempted Isnumeric(BotCell.Offset(0, -1).Value) = True
and the loop stayed true all the way to 65,536 !

BotCell.Offset(0, -1) is a column of numbers in every cell until the data
stops (at Row 638). I did not think to tell you that.

My question though why does:

Isnumeric(BotCell.Offset(0, -1).Value) evaluate to true
when .Offset(0, -1).Value is a blank cell? After E638 to end)

Dennis
If TopCell.Value = "" Then
'do nothing
Else
Do
If Len(BotCell.Offset(0, -1).Value) > 0 Then
Set BotCell = BotCell.Offset(1, 0)
Else
Exit Do
End If
Loop
Set MyRngToCopy = .Range(TopCell, BotCell).Resize(, 2)
End If


"Dave Peterson" wrote:

> 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(xlCellTypeFormulas)
> > > > 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
>

VBA treats empty cells just like excel. But formulas that evaluate to ""
shouldn't look like 0's.

If A1 is empty and B1 has 33, then =A1+B1 evaluates to 33.

You can check in a couple of ways:

if isempty(somerange) = false _
and isnumeric(somerange.value) then

or you could use Excel's =IsNumber() worksheet function:

if application.isnumber(somerange.value) = false then
'it's not a number
else
'....


But I thought you said the formulas showed "".




Dennis wrote:
>
> Dave,
>
> Thanks again for your help and insight.
>
> I made a change in the first lin of the "IF" Statement as testing for ""
> stopped the calculated(ing) range at one row.
>
> In the process,
> I attempted Isnumeric(BotCell.Offset(0, -1).Value) = True
> and the loop stayed true all the way to 65,536 !
>
> BotCell.Offset(0, -1) is a column of numbers in every cell until the data
> stops (at Row 638). I did not think to tell you that.
>
> My question though why does:
>
> Isnumeric(BotCell.Offset(0, -1).Value) evaluate to true
> when .Offset(0, -1).Value is a blank cell? After E638 to end)
>
> Dennis
> If TopCell.Value = "" Then
> 'do nothing
> Else
> Do
> If Len(BotCell.Offset(0, -1).Value) > 0 Then
> Set BotCell = BotCell.Offset(1, 0)
> Else
> Exit Do
> End If
> Loop
> Set MyRngToCopy = .Range(TopCell, BotCell).Resize(, 2)
> End If
>
<<snipped>>

You are correct that many of the cells in the computed range evaluate to "".
The cells that I wanted were those which did not = "". I was not clear
enough.

I had a Pivot Table which, depending upon the Autofilter choices, displayed
information in a table of varying length.
The quick but dirty first solution was to copy down formulas in cols F & G
20,000 down to display in columns to the right of the Pivot-table itself.
(The longest table display was 18,000+)

Later I wanted to automate it in VBA plus I was copying far too many cells
without meaninful data. Thus I wanted a way to limit the "To Copy" range
since the data stopped usually far less than 20,000 cells down.

Your information was crutial re: structure and method.

What I should have told you was that in Col E was either subtotals or dollar
info. That is why I attempted the IsNumeric but settled on the Len() >0.

BTW, your aditional information is extremely interesting as it causes me to
want to experiment. This stuff will never sink in for me if I do not value
what is happening behind the "screen."

Thanks!




Yesterday, after

"Dave Peterson" wrote:

> VBA treats empty cells just like excel. But formulas that evaluate to ""
> shouldn't look like 0's.
>
> If A1 is empty and B1 has 33, then =A1+B1 evaluates to 33.
>
> You can check in a couple of ways:
>
> if isempty(somerange) = false _
> and isnumeric(somerange.value) then
>
> or you could use Excel's =IsNumber() worksheet function:
>
> if application.isnumber(somerange.value) = false then
> 'it's not a number
> else
> '....
>
>
> But I thought you said the formulas showed "".
>
>
>
>
> Dennis wrote:
> >
> > Dave,
> >
> > Thanks again for your help and insight.
> >
> > I made a change in the first lin of the "IF" Statement as testing for ""
> > stopped the calculated(ing) range at one row.
> >
> > In the process,
> > I attempted Isnumeric(BotCell.Offset(0, -1).Value) = True
> > and the loop stayed true all the way to 65,536 !
> >
> > BotCell.Offset(0, -1) is a column of numbers in every cell until the data
> > stops (at Row 638). I did not think to tell you that.
> >
> > My question though why does:
> >
> > Isnumeric(BotCell.Offset(0, -1).Value) evaluate to true
> > when .Offset(0, -1).Value is a blank cell? After E638 to end)
> >
> > Dennis
> > If TopCell.Value = "" Then
> > 'do nothing
> > Else
> > Do
> > If Len(BotCell.Offset(0, -1).Value) > 0 Then
> > Set BotCell = BotCell.Offset(1, 0)
> > Else
> > Exit Do
> > End If
> > Loop
> > Set MyRngToCopy = .Range(TopCell, BotCell).Resize(, 2)
> > End If
> >
> <<snipped>>
>

It sounds like it's working <vbg>.

Dennis wrote:
>
> You are correct that many of the cells in the computed range evaluate to "".
> The cells that I wanted were those which did not = "". I was not clear
> enough.
>
> I had a Pivot Table which, depending upon the Autofilter choices, displayed
> information in a table of varying length.
> The quick but dirty first solution was to copy down formulas in cols F & G
> 20,000 down to display in columns to the right of the Pivot-table itself.
> (The longest table display was 18,000+)
>
> Later I wanted to automate it in VBA plus I was copying far too many cells
> without meaninful data. Thus I wanted a way to limit the "To Copy" range
> since the data stopped usually far less than 20,000 cells down.
>
> Your information was crutial re: structure and method.
>
> What I should have told you was that in Col E was either subtotals or dollar
> info. That is why I attempted the IsNumeric but settled on the Len() >0.
>
> BTW, your aditional information is extremely interesting as it causes me to
> want to experiment. This stuff will never sink in for me if I do not value
> what is happening behind the "screen."
>
> Thanks!
>
>
>
<<snipped>>
--

Dave Peterson