software

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(quot;Pivot Tablequot;).Range(Cells(5, quot;Fquot;).Address, Cells _
(Rows.Count, quot;Gquot;).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 quot;Wasted-spacequot; 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, DennisIf 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(quot;pivot tablequot;)
Set myRngToCopy = Nothing
On Error Resume Next
Set myRngToCopy = .Range(quot;f5:Gquot; amp; .Cells(.Rows.Count, quot;Gquot;).End(xlUp).Row) _
.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

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

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

Dennis wrote:
gt;
gt; Using 2003
gt;
gt; Sheet1: Current macro calculates a range of cells from a fixed upper-left
gt; range cells F5 to G200.: (Note that Sheet1 is a Pivot-table already using
gt; Auto filter.)
gt;
gt; Sheets(quot;Pivot Tablequot;).Range(Cells(5, quot;Fquot;).Address, Cells _
gt; (Rows.Count, quot;Gquot;).End(xlUp).Address).Copy
gt;
gt; (followed by
gt;
gt; Selection.PasteSpecial Paste:=xlPasteValues,
gt; Operation:=xlNone, SkipBlanks:=True, Transpose:=False
gt;
gt; I would prefer that the copy command would evaluate only the cells whose
gt; formula calculates a value. By this I mean: Many of the cells in the F5 to
gt; G200 range do not have a calculated value. Assuming that only 5 rows have
gt; values, the copy command will copy the calculated range F5 to G200, meaning
gt; that 195 two-column cell combinations will be pasted to the receiving range
gt; wasting space on Sheet2.
gt;
gt; My macros then copy another range from Sheet3 beginning at row 201. (Again,
gt; there are 195 essentially wasted rows between the cells pasted from Sheet1)
gt;
gt; I have attempted to Copy using VisibleCells and/or Paste-special SkipBlanks
gt; = True - yet I still get the 195 quot;Wasted-spacequot; cells.
gt;
gt; It seems that there are three solutions:
gt; One is a smarter way to copy only the cells that the
gt; underlying formula calculates a value.
gt;
gt; -or-
gt;
gt; Another, is to Paste only those cells from Sheet1 which
gt; have values (text or numeric)
gt;
gt; -or-
gt;
gt; Develope a routine to delete the empty rows on Sheet2.
gt;
gt; Which of the three is the smartest/most efficient?
gt;
gt; I need help with coding the syntax in VBA.
gt;
gt; Thanks, Dennis

--

Dave Peterson

Thanks Dave!

I inserted your code into my macro as follows.

With Worksheets(quot;Pivot Tablequot;)
Set MyRngToCopy = Nothing
On Error Resume Next
Set MyRngToCopy = .Range(quot;F5:Gquot; amp; .Cells (.Rows.Count, quot;Gquot;)
.End(xlUp).Row).Cells.SpecialCells(xlCellTypeFormu las)
On Error GoTo 0
If MyRngToCopy Is Nothing Then
MsgBox quot;Nothing to Copyquot;
Else
Sheets(MySheet).Select '?? Could this confuse the quot;Selectionquot;
Range(quot;B239quot;).Select '?? Could this confuse the quot;Selectionquot;
MyRngToCopy.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End If
End With

The copy process still inserts quot;phantomquot; 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),quot;quot;,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 quot;selections.quot; to the
MyRngToCopy.Copy
process. BTW, I moved those other selections out and in the quot;Withquot; process
with the same result. Do we need possibly ....
SpecialCells(xlCellTypeFormulas, XX)?

Dennis

quot;Dave Petersonquot; wrote:

gt; If you try this by hand, does it select the cells that you want copied? And if
gt; it does, is that range a nice rectangular contiguous block of cells?
gt;
gt; Select F5:Gxxx
gt; edit|goto|special
gt; formulas
gt; click ok.
gt;
gt; If it does, you could do that same kind of thing in code.
gt;
gt; Dim myRngToCopy As Range
gt;
gt; With Worksheets(quot;pivot tablequot;)
gt; Set myRngToCopy = Nothing
gt; On Error Resume Next
gt; Set myRngToCopy = .Range(quot;f5:Gquot; amp; .Cells(.Rows.Count, quot;Gquot;).End(xlUp).Row) _
gt; .Cells.SpecialCells(xlCellTypeFormulas)
gt; On Error GoTo 0
gt;
gt; If myRngToCopy Is Nothing Then
gt; MsgBox quot;nothing to copyquot;
gt; Else
gt; myRngToCopy.Copy
gt; 'later...
gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; End If
gt;
gt; =========
gt; Did you really mean you had formulas in that F5:Gxx range???
gt;
gt; Dennis wrote:
gt; gt;
gt; gt; Using 2003
gt; gt;
gt; gt; Sheet1: Current macro calculates a range of cells from a fixed upper-left
gt; gt; range cells F5 to G200.: (Note that Sheet1 is a Pivot-table already using
gt; gt; Auto filter.)
gt; gt;
gt; gt; Sheets(quot;Pivot Tablequot;).Range(Cells(5, quot;Fquot;).Address, Cells _
gt; gt; (Rows.Count, quot;Gquot;).End(xlUp).Address).Copy
gt; gt;
gt; gt; (followed by
gt; gt;
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues,
gt; gt; Operation:=xlNone, SkipBlanks:=True, Transpose:=False
gt; gt;
gt; gt; I would prefer that the copy command would evaluate only the cells whose
gt; gt; formula calculates a value. By this I mean: Many of the cells in the F5 to
gt; gt; G200 range do not have a calculated value. Assuming that only 5 rows have
gt; gt; values, the copy command will copy the calculated range F5 to G200, meaning
gt; gt; that 195 two-column cell combinations will be pasted to the receiving range
gt; gt; wasting space on Sheet2.
gt; gt;
gt; gt; My macros then copy another range from Sheet3 beginning at row 201. (Again,
gt; gt; there are 195 essentially wasted rows between the cells pasted from Sheet1)
gt; gt;
gt; gt; I have attempted to Copy using VisibleCells and/or Paste-special SkipBlanks
gt; gt; = True - yet I still get the 195 quot;Wasted-spacequot; cells.
gt; gt;
gt; gt; It seems that there are three solutions:
gt; gt; One is a smarter way to copy only the cells that the
gt; gt; underlying formula calculates a value.
gt; gt;
gt; gt; -or-
gt; gt;
gt; gt; Another, is to Paste only those cells from Sheet1 which
gt; gt; have values (text or numeric)
gt; gt;
gt; gt; -or-
gt; gt;
gt; gt; Develope a routine to delete the empty rows on Sheet2.
gt; gt;
gt; gt; Which of the three is the smartest/most efficient?
gt; gt;
gt; gt; I need help with coding the syntax in VBA.
gt; gt;
gt; gt; Thanks, Dennis
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

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

I would think the quickest solution would be just to look down column F looking
for quot;quot;. 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(quot;Pivot tablequot;)
Set myRngToCopy = Nothing
Set TopCell = .Range(quot;F5quot;)
Set BotCell = TopCell
If TopCell.Value = quot;quot; Then
'do nothing
Else
Do
If BotCell.Offset(1, 0).Value = quot;quot; 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(quot;mysheetquot;).Range(quot;b239quot;).PasteSpecial
Paste:=xlPasteValues
End If
End With

End Sub

Dennis wrote:
gt;
gt; Thanks Dave!
gt;
gt; I inserted your code into my macro as follows.
gt;
gt; With Worksheets(quot;Pivot Tablequot;)
gt; Set MyRngToCopy = Nothing
gt; On Error Resume Next
gt; Set MyRngToCopy = .Range(quot;F5:Gquot; amp; .Cells (.Rows.Count, quot;Gquot;)
gt; .End(xlUp).Row).Cells.SpecialCells(xlCellTypeFormu las)
gt; On Error GoTo 0
gt; If MyRngToCopy Is Nothing Then
gt; MsgBox quot;Nothing to Copyquot;
gt; Else
gt; Sheets(MySheet).Select '?? Could this confuse the quot;Selectionquot;
gt; Range(quot;B239quot;).Select '?? Could this confuse the quot;Selectionquot;
gt; MyRngToCopy.Copy
gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; End If
gt; End With
gt;
gt; The copy process still inserts quot;phantomquot; cells. The cells to copy from are
gt; formula cells - just with no value. The formula in the cells being copied
gt; from is: =IF(ISBLANK(B2),quot;quot;,B2)
gt;
gt; On the receiving sheet, XL does not see formulas or blanks (in the subject
gt; area) but does see constants (using the GoTo routine).
gt;
gt; Note above my comments about the the prior quot;selections.quot; to the
gt; MyRngToCopy.Copy
gt; process. BTW, I moved those other selections out and in the quot;Withquot; process
gt; with the same result. Do we need possibly ....
gt; SpecialCells(xlCellTypeFormulas, XX)?
gt;
gt; Dennis
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; If you try this by hand, does it select the cells that you want copied? And if
gt; gt; it does, is that range a nice rectangular contiguous block of cells?
gt; gt;
gt; gt; Select F5:Gxxx
gt; gt; edit|goto|special
gt; gt; formulas
gt; gt; click ok.
gt; gt;
gt; gt; If it does, you could do that same kind of thing in code.
gt; gt;
gt; gt; Dim myRngToCopy As Range
gt; gt;
gt; gt; With Worksheets(quot;pivot tablequot;)
gt; gt; Set myRngToCopy = Nothing
gt; gt; On Error Resume Next
gt; gt; Set myRngToCopy = .Range(quot;f5:Gquot; amp; .Cells(.Rows.Count, quot;Gquot;).End(xlUp).Row) _
gt; gt; .Cells.SpecialCells(xlCellTypeFormulas)
gt; gt; On Error GoTo 0
gt; gt;
gt; gt; If myRngToCopy Is Nothing Then
gt; gt; MsgBox quot;nothing to copyquot;
gt; gt; Else
gt; gt; myRngToCopy.Copy
gt; gt; 'later...
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; End If
gt; gt;
gt; gt; =========
gt; gt; Did you really mean you had formulas in that F5:Gxx range???
gt; gt;
gt; gt; Dennis wrote:
gt; gt; gt;
gt; gt; gt; Using 2003
gt; gt; gt;
gt; gt; gt; Sheet1: Current macro calculates a range of cells from a fixed upper-left
gt; gt; gt; range cells F5 to G200.: (Note that Sheet1 is a Pivot-table already using
gt; gt; gt; Auto filter.)
gt; gt; gt;
gt; gt; gt; Sheets(quot;Pivot Tablequot;).Range(Cells(5, quot;Fquot;).Address, Cells _
gt; gt; gt; (Rows.Count, quot;Gquot;).End(xlUp).Address).Copy
gt; gt; gt;
gt; gt; gt; (followed by
gt; gt; gt;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues,
gt; gt; gt; Operation:=xlNone, SkipBlanks:=True, Transpose:=False
gt; gt; gt;
gt; gt; gt; I would prefer that the copy command would evaluate only the cells whose
gt; gt; gt; formula calculates a value. By this I mean: Many of the cells in the F5 to
gt; gt; gt; G200 range do not have a calculated value. Assuming that only 5 rows have
gt; gt; gt; values, the copy command will copy the calculated range F5 to G200, meaning
gt; gt; gt; that 195 two-column cell combinations will be pasted to the receiving range
gt; gt; gt; wasting space on Sheet2.
gt; gt; gt;
gt; gt; gt; My macros then copy another range from Sheet3 beginning at row 201. (Again,
gt; gt; gt; there are 195 essentially wasted rows between the cells pasted from Sheet1)
gt; gt; gt;
gt; gt; gt; I have attempted to Copy using VisibleCells and/or Paste-special SkipBlanks
gt; gt; gt; = True - yet I still get the 195 quot;Wasted-spacequot; cells.
gt; gt; gt;
gt; gt; gt; It seems that there are three solutions:
gt; gt; gt; One is a smarter way to copy only the cells that the
gt; gt; gt; underlying formula calculates a value.
gt; gt; gt;
gt; gt; gt; -or-
gt; gt; gt;
gt; gt; gt; Another, is to Paste only those cells from Sheet1 which
gt; gt; gt; have values (text or numeric)
gt; gt; gt;
gt; gt; gt; -or-
gt; gt; gt;
gt; gt; gt; Develope a routine to delete the empty rows on Sheet2.
gt; gt; gt;
gt; gt; gt; Which of the three is the smartest/most efficient?
gt; gt; gt;
gt; gt; gt; I need help with coding the syntax in VBA.
gt; gt; gt;
gt; gt; gt; Thanks, Dennis
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Dave, I did not realize that quot;quot; was actually entered into the cell further
assuming ( ha! ha! on me) quot;quot; was a code to XL to enter quot;nothing.quot; Now, I
understand why blank cells are not necessarily blank even though I do not see
a value or quot;formulaquot; in the formula bar.

Please check back tomorrow I am off to a meeting.

Thank you very much.quot;Dave Petersonquot; wrote:

gt; The cells aren't empty. They contain formulas that evaluate to quot;quot;.
gt;
gt; So that .specialcells() stuff won't help.
gt;
gt; I would think the quickest solution would be just to look down column F looking
gt; for quot;quot;. As soon as you find it, go back up a row and do the copy.
gt;
gt; This may work if your range to copy is contiguous.
gt;
gt; Option Explicit
gt; Sub testm()
gt;
gt; Dim TopCell As Range
gt; Dim BotCell As Range
gt; Dim myRngToCopy As Range
gt;
gt; With Worksheets(quot;Pivot tablequot;)
gt; Set myRngToCopy = Nothing
gt; Set TopCell = .Range(quot;F5quot;)
gt; Set BotCell = TopCell
gt; If TopCell.Value = quot;quot; Then
gt; 'do nothing
gt; Else
gt; Do
gt; If BotCell.Offset(1, 0).Value = quot;quot; Then
gt; Exit Do
gt; Else
gt; Set BotCell = BotCell.Offset(1, 0)
gt; End If
gt; Loop
gt; Set myRngToCopy = .Range(TopCell, BotCell).Resize(, 2) '2 columns
gt; End If
gt;
gt; If myRngToCopy Is Nothing Then
gt; 'nice msgbox
gt; Else
gt; myRngToCopy.Copy
gt; Worksheets(quot;mysheetquot;).Range(quot;b239quot;).PasteSpecial
gt; Paste:=xlPasteValues
gt; End If
gt; End With
gt;
gt; End Sub
gt;
gt;
gt;
gt;
gt; Dennis wrote:
gt; gt;
gt; gt; Thanks Dave!
gt; gt;
gt; gt; I inserted your code into my macro as follows.
gt; gt;
gt; gt; With Worksheets(quot;Pivot Tablequot;)
gt; gt; Set MyRngToCopy = Nothing
gt; gt; On Error Resume Next
gt; gt; Set MyRngToCopy = .Range(quot;F5:Gquot; amp; .Cells (.Rows.Count, quot;Gquot;)
gt; gt; .End(xlUp).Row).Cells.SpecialCells(xlCellTypeFormu las)
gt; gt; On Error GoTo 0
gt; gt; If MyRngToCopy Is Nothing Then
gt; gt; MsgBox quot;Nothing to Copyquot;
gt; gt; Else
gt; gt; Sheets(MySheet).Select '?? Could this confuse the quot;Selectionquot;
gt; gt; Range(quot;B239quot;).Select '?? Could this confuse the quot;Selectionquot;
gt; gt; MyRngToCopy.Copy
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; End If
gt; gt; End With
gt; gt;
gt; gt; The copy process still inserts quot;phantomquot; cells. The cells to copy from are
gt; gt; formula cells - just with no value. The formula in the cells being copied
gt; gt; from is: =IF(ISBLANK(B2),quot;quot;,B2)
gt; gt;
gt; gt; On the receiving sheet, XL does not see formulas or blanks (in the subject
gt; gt; area) but does see constants (using the GoTo routine).
gt; gt;
gt; gt; Note above my comments about the the prior quot;selections.quot; to the
gt; gt; MyRngToCopy.Copy
gt; gt; process. BTW, I moved those other selections out and in the quot;Withquot; process
gt; gt; with the same result. Do we need possibly ....
gt; gt; SpecialCells(xlCellTypeFormulas, XX)?
gt; gt;
gt; gt; Dennis
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; If you try this by hand, does it select the cells that you want copied? And if
gt; gt; gt; it does, is that range a nice rectangular contiguous block of cells?
gt; gt; gt;
gt; gt; gt; Select F5:Gxxx
gt; gt; gt; edit|goto|special
gt; gt; gt; formulas
gt; gt; gt; click ok.
gt; gt; gt;
gt; gt; gt; If it does, you could do that same kind of thing in code.
gt; gt; gt;
gt; gt; gt; Dim myRngToCopy As Range
gt; gt; gt;
gt; gt; gt; With Worksheets(quot;pivot tablequot;)
gt; gt; gt; Set myRngToCopy = Nothing
gt; gt; gt; On Error Resume Next
gt; gt; gt; Set myRngToCopy = .Range(quot;f5:Gquot; amp; .Cells(.Rows.Count, quot;Gquot;).End(xlUp).Row) _
gt; gt; gt; .Cells.SpecialCells(xlCellTypeFormulas)
gt; gt; gt; On Error GoTo 0
gt; gt; gt;
gt; gt; gt; If myRngToCopy Is Nothing Then
gt; gt; gt; MsgBox quot;nothing to copyquot;
gt; gt; gt; Else
gt; gt; gt; myRngToCopy.Copy
gt; gt; gt; 'later...
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; =========
gt; gt; gt; Did you really mean you had formulas in that F5:Gxx range???
gt; gt; gt;
gt; gt; gt; Dennis wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Using 2003
gt; gt; gt; gt;
gt; gt; gt; gt; Sheet1: Current macro calculates a range of cells from a fixed upper-left
gt; gt; gt; gt; range cells F5 to G200.: (Note that Sheet1 is a Pivot-table already using
gt; gt; gt; gt; Auto filter.)
gt; gt; gt; gt;
gt; gt; gt; gt; Sheets(quot;Pivot Tablequot;).Range(Cells(5, quot;Fquot;).Address, Cells _
gt; gt; gt; gt; (Rows.Count, quot;Gquot;).End(xlUp).Address).Copy
gt; gt; gt; gt;
gt; gt; gt; gt; (followed by
gt; gt; gt; gt;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues,
gt; gt; gt; gt; Operation:=xlNone, SkipBlanks:=True, Transpose:=False
gt; gt; gt; gt;
gt; gt; gt; gt; I would prefer that the copy command would evaluate only the cells whose
gt; gt; gt; gt; formula calculates a value. By this I mean: Many of the cells in the F5 to
gt; gt; gt; gt; G200 range do not have a calculated value. Assuming that only 5 rows have
gt; gt; gt; gt; values, the copy command will copy the calculated range F5 to G200, meaning
gt; gt; gt; gt; that 195 two-column cell combinations will be pasted to the receiving range
gt; gt; gt; gt; wasting space on Sheet2.
gt; gt; gt; gt;
gt; gt; gt; gt; My macros then copy another range from Sheet3 beginning at row 201. (Again,
gt; gt; gt; gt; there are 195 essentially wasted rows between the cells pasted from Sheet1)
gt; gt; gt; gt;
gt; gt; gt; gt; I have attempted to Copy using VisibleCells and/or Paste-special SkipBlanks
gt; gt; gt; gt; = True - yet I still get the 195 quot;Wasted-spacequot; cells.
gt; gt; gt; gt;
gt; gt; gt; gt; It seems that there are three solutions:
gt; gt; gt; gt; One is a smarter way to copy only the cells that the
gt; gt; gt; gt; underlying formula calculates a value.
gt; gt; gt; gt;
gt; gt; gt; gt; -or-
gt; gt; gt; gt;
gt; gt; gt; gt; Another, is to Paste only those cells from Sheet1 which
gt; gt; gt; gt; have values (text or numeric)
gt; gt; gt; gt;
gt; gt; gt; gt; -or-
gt; gt; gt; gt;
gt; gt; gt; gt; Develope a routine to delete the empty rows on Sheet2.
gt; gt; gt; gt;
gt; gt; gt; gt; Which of the three is the smartest/most efficient?
gt; gt; gt; gt;
gt; gt; gt; gt; I need help with coding the syntax in VBA.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks, Dennis
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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 quot;quot; (like =IF(ISBLANK(B2),quot;quot;,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.

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

quot;Dave Petersonquot; wrote:

gt; The cells aren't empty. They contain formulas that evaluate to quot;quot;.
gt;
gt; So that .specialcells() stuff won't help.
gt;
gt; I would think the quickest solution would be just to look down column F looking
gt; for quot;quot;. As soon as you find it, go back up a row and do the copy.
gt;
gt; This may work if your range to copy is contiguous.
gt;
gt; Option Explicit
gt; Sub testm()
gt;
gt; Dim TopCell As Range
gt; Dim BotCell As Range
gt; Dim myRngToCopy As Range
gt;
gt; With Worksheets(quot;Pivot tablequot;)
gt; Set myRngToCopy = Nothing
gt; Set TopCell = .Range(quot;F5quot;)
gt; Set BotCell = TopCell
gt; If TopCell.Value = quot;quot; Then
gt; 'do nothing
gt; Else
gt; Do
gt; If BotCell.Offset(1, 0).Value = quot;quot; Then
gt; Exit Do
gt; Else
gt; Set BotCell = BotCell.Offset(1, 0)
gt; End If
gt; Loop
gt; Set myRngToCopy = .Range(TopCell, BotCell).Resize(, 2) '2 columns
gt; End If
gt;
gt; If myRngToCopy Is Nothing Then
gt; 'nice msgbox
gt; Else
gt; myRngToCopy.Copy
gt; Worksheets(quot;mysheetquot;).Range(quot;b239quot;).PasteSpecial
gt; Paste:=xlPasteValues
gt; End If
gt; End With
gt;
gt; End Sub
gt;
gt;
gt;
gt;
gt; Dennis wrote:
gt; gt;
gt; gt; Thanks Dave!
gt; gt;
gt; gt; I inserted your code into my macro as follows.
gt; gt;
gt; gt; With Worksheets(quot;Pivot Tablequot;)
gt; gt; Set MyRngToCopy = Nothing
gt; gt; On Error Resume Next
gt; gt; Set MyRngToCopy = .Range(quot;F5:Gquot; amp; .Cells (.Rows.Count, quot;Gquot;)
gt; gt; .End(xlUp).Row).Cells.SpecialCells(xlCellTypeFormu las)
gt; gt; On Error GoTo 0
gt; gt; If MyRngToCopy Is Nothing Then
gt; gt; MsgBox quot;Nothing to Copyquot;
gt; gt; Else
gt; gt; Sheets(MySheet).Select '?? Could this confuse the quot;Selectionquot;
gt; gt; Range(quot;B239quot;).Select '?? Could this confuse the quot;Selectionquot;
gt; gt; MyRngToCopy.Copy
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; End If
gt; gt; End With
gt; gt;
gt; gt; The copy process still inserts quot;phantomquot; cells. The cells to copy from are
gt; gt; formula cells - just with no value. The formula in the cells being copied
gt; gt; from is: =IF(ISBLANK(B2),quot;quot;,B2)
gt; gt;
gt; gt; On the receiving sheet, XL does not see formulas or blanks (in the subject
gt; gt; area) but does see constants (using the GoTo routine).
gt; gt;
gt; gt; Note above my comments about the the prior quot;selections.quot; to the
gt; gt; MyRngToCopy.Copy
gt; gt; process. BTW, I moved those other selections out and in the quot;Withquot; process
gt; gt; with the same result. Do we need possibly ....
gt; gt; SpecialCells(xlCellTypeFormulas, XX)?
gt; gt;
gt; gt; Dennis
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; If you try this by hand, does it select the cells that you want copied? And if
gt; gt; gt; it does, is that range a nice rectangular contiguous block of cells?
gt; gt; gt;
gt; gt; gt; Select F5:Gxxx
gt; gt; gt; edit|goto|special
gt; gt; gt; formulas
gt; gt; gt; click ok.
gt; gt; gt;
gt; gt; gt; If it does, you could do that same kind of thing in code.
gt; gt; gt;
gt; gt; gt; Dim myRngToCopy As Range
gt; gt; gt;
gt; gt; gt; With Worksheets(quot;pivot tablequot;)
gt; gt; gt; Set myRngToCopy = Nothing
gt; gt; gt; On Error Resume Next
gt; gt; gt; Set myRngToCopy = .Range(quot;f5:Gquot; amp; .Cells(.Rows.Count, quot;Gquot;).End(xlUp).Row) _
gt; gt; gt; .Cells.SpecialCells(xlCellTypeFormulas)
gt; gt; gt; On Error GoTo 0
gt; gt; gt;
gt; gt; gt; If myRngToCopy Is Nothing Then
gt; gt; gt; MsgBox quot;nothing to copyquot;
gt; gt; gt; Else
gt; gt; gt; myRngToCopy.Copy
gt; gt; gt; 'later...
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; =========
gt; gt; gt; Did you really mean you had formulas in that F5:Gxx range???
gt; gt; gt;
gt; gt; gt; Dennis wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Using 2003
gt; gt; gt; gt;
gt; gt; gt; gt; Sheet1: Current macro calculates a range of cells from a fixed upper-left
gt; gt; gt; gt; range cells F5 to G200.: (Note that Sheet1 is a Pivot-table already using
gt; gt; gt; gt; Auto filter.)
gt; gt; gt; gt;
gt; gt; gt; gt; Sheets(quot;Pivot Tablequot;).Range(Cells(5, quot;Fquot;).Address, Cells _
gt; gt; gt; gt; (Rows.Count, quot;Gquot;).End(xlUp).Address).Copy
gt; gt; gt; gt;
gt; gt; gt; gt; (followed by
gt; gt; gt; gt;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues,
gt; gt; gt; gt; Operation:=xlNone, SkipBlanks:=True, Transpose:=False
gt; gt; gt; gt;
gt; gt; gt; gt; I would prefer that the copy command would evaluate only the cells whose
gt; gt; gt; gt; formula calculates a value. By this I mean: Many of the cells in the F5 to
gt; gt; gt; gt; G200 range do not have a calculated value. Assuming that only 5 rows have
gt; gt; gt; gt; values, the copy command will copy the calculated range F5 to G200, meaning
gt; gt; gt; gt; that 195 two-column cell combinations will be pasted to the receiving range
gt; gt; gt; gt; wasting space on Sheet2.
gt; gt; gt; gt;
gt; gt; gt; gt; My macros then copy another range from Sheet3 beginning at row 201. (Again,
gt; gt; gt; gt; there are 195 essentially wasted rows between the cells pasted from Sheet1)
gt; gt; gt; gt;
gt; gt; gt; gt; I have attempted to Copy using VisibleCells and/or Paste-special SkipBlanks
gt; gt; gt; gt; = True - yet I still get the 195 quot;Wasted-spacequot; cells.
gt; gt; gt; gt;
gt; gt; gt; gt; It seems that there are three solutions:
gt; gt; gt; gt; One is a smarter way to copy only the cells that the
gt; gt; gt; gt; underlying formula calculates a value.
gt; gt; gt; gt;
gt; gt; gt; gt; -or-
gt; gt; gt; gt;
gt; gt; gt; gt; Another, is to Paste only those cells from Sheet1 which
gt; gt; gt; gt; have values (text or numeric)
gt; gt; gt; gt;
gt; gt; gt; gt; -or-
gt; gt; gt; gt;
gt; gt; gt; gt; Develope a routine to delete the empty rows on Sheet2.
gt; gt; gt; gt;
gt; gt; gt; gt; Which of the three is the smartest/most efficient?
gt; gt; gt; gt;
gt; gt; gt; gt; I need help with coding the syntax in VBA.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks, Dennis
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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:
gt;
gt; For others who may read this thread.
gt;
gt; If you Copy/Paste's (values-only), when the source range contains formulas
gt; that set the cell(s) value(s) to quot;quot; (like =IF(ISBLANK(B2),quot;quot;,B2), the
gt; Pasted-to cell range can become part of the used-range (at least as it
gt; relates to functions using .End(xlUp or xlDown or xlRight or xlLeft).
gt;
gt; The receiving cells (of the combined copy/paste procedure) do not contain
gt; any visible values or formulas, but to XL the cells are not the same as
gt; never-used cells.
gt;
gt; **********************************************
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; The cells aren't empty. They contain formulas that evaluate to quot;quot;.
gt; gt;
gt; gt; So that .specialcells() stuff won't help.
gt; gt;
gt; gt; I would think the quickest solution would be just to look down column F looking
gt; gt; for quot;quot;. As soon as you find it, go back up a row and do the copy.
gt; gt;
gt; gt; This may work if your range to copy is contiguous.
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testm()
gt; gt;
gt; gt; Dim TopCell As Range
gt; gt; Dim BotCell As Range
gt; gt; Dim myRngToCopy As Range
gt; gt;
gt; gt; With Worksheets(quot;Pivot tablequot;)
gt; gt; Set myRngToCopy = Nothing
gt; gt; Set TopCell = .Range(quot;F5quot;)
gt; gt; Set BotCell = TopCell
gt; gt; If TopCell.Value = quot;quot; Then
gt; gt; 'do nothing
gt; gt; Else
gt; gt; Do
gt; gt; If BotCell.Offset(1, 0).Value = quot;quot; Then
gt; gt; Exit Do
gt; gt; Else
gt; gt; Set BotCell = BotCell.Offset(1, 0)
gt; gt; End If
gt; gt; Loop
gt; gt; Set myRngToCopy = .Range(TopCell, BotCell).Resize(, 2) '2 columns
gt; gt; End If
gt; gt;
gt; gt; If myRngToCopy Is Nothing Then
gt; gt; 'nice msgbox
gt; gt; Else
gt; gt; myRngToCopy.Copy
gt; gt; Worksheets(quot;mysheetquot;).Range(quot;b239quot;).PasteSpecial
gt; gt; Paste:=xlPasteValues
gt; gt; End If
gt; gt; End With
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; Dennis wrote:
gt; gt; gt;
gt; gt; gt; Thanks Dave!
gt; gt; gt;
gt; gt; gt; I inserted your code into my macro as follows.
gt; gt; gt;
gt; gt; gt; With Worksheets(quot;Pivot Tablequot;)
gt; gt; gt; Set MyRngToCopy = Nothing
gt; gt; gt; On Error Resume Next
gt; gt; gt; Set MyRngToCopy = .Range(quot;F5:Gquot; amp; .Cells (.Rows.Count, quot;Gquot;)
gt; gt; gt; .End(xlUp).Row).Cells.SpecialCells(xlCellTypeFormu las)
gt; gt; gt; On Error GoTo 0
gt; gt; gt; If MyRngToCopy Is Nothing Then
gt; gt; gt; MsgBox quot;Nothing to Copyquot;
gt; gt; gt; Else
gt; gt; gt; Sheets(MySheet).Select '?? Could this confuse the quot;Selectionquot;
gt; gt; gt; Range(quot;B239quot;).Select '?? Could this confuse the quot;Selectionquot;
gt; gt; gt; MyRngToCopy.Copy
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; gt; End If
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; The copy process still inserts quot;phantomquot; cells. The cells to copy from are
gt; gt; gt; formula cells - just with no value. The formula in the cells being copied
gt; gt; gt; from is: =IF(ISBLANK(B2),quot;quot;,B2)
gt; gt; gt;
gt; gt; gt; On the receiving sheet, XL does not see formulas or blanks (in the subject
gt; gt; gt; area) but does see constants (using the GoTo routine).
gt; gt; gt;
gt; gt; gt; Note above my comments about the the prior quot;selections.quot; to the
gt; gt; gt; MyRngToCopy.Copy
gt; gt; gt; process. BTW, I moved those other selections out and in the quot;Withquot; process
gt; gt; gt; with the same result. Do we need possibly ....
gt; gt; gt; SpecialCells(xlCellTypeFormulas, XX)?
gt; gt; gt;
gt; gt; gt; Dennis
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If you try this by hand, does it select the cells that you want copied? And if
gt; gt; gt; gt; it does, is that range a nice rectangular contiguous block of cells?
gt; gt; gt; gt;
gt; gt; gt; gt; Select F5:Gxxx
gt; gt; gt; gt; edit|goto|special
gt; gt; gt; gt; formulas
gt; gt; gt; gt; click ok.
gt; gt; gt; gt;
gt; gt; gt; gt; If it does, you could do that same kind of thing in code.
gt; gt; gt; gt;
gt; gt; gt; gt; Dim myRngToCopy As Range
gt; gt; gt; gt;
gt; gt; gt; gt; With Worksheets(quot;pivot tablequot;)
gt; gt; gt; gt; Set myRngToCopy = Nothing
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; Set myRngToCopy = .Range(quot;f5:Gquot; amp; .Cells(.Rows.Count, quot;Gquot;).End(xlUp).Row) _
gt; gt; gt; gt; .Cells.SpecialCells(xlCellTypeFormulas)
gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt;
gt; gt; gt; gt; If myRngToCopy Is Nothing Then
gt; gt; gt; gt; MsgBox quot;nothing to copyquot;
gt; gt; gt; gt; Else
gt; gt; gt; gt; myRngToCopy.Copy
gt; gt; gt; gt; 'later...
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; gt; gt; End If
gt; gt; gt; gt;
gt; gt; gt; gt; =========
gt; gt; gt; gt; Did you really mean you had formulas in that F5:Gxx range???
gt; gt; gt; gt;
gt; gt; gt; gt; Dennis wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Using 2003
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sheet1: Current macro calculates a range of cells from a fixed upper-left
gt; gt; gt; gt; gt; range cells F5 to G200.: (Note that Sheet1 is a Pivot-table already using
gt; gt; gt; gt; gt; Auto filter.)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sheets(quot;Pivot Tablequot;).Range(Cells(5, quot;Fquot;).Address, Cells _
gt; gt; gt; gt; gt; (Rows.Count, quot;Gquot;).End(xlUp).Address).Copy
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; (followed by
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues,
gt; gt; gt; gt; gt; Operation:=xlNone, SkipBlanks:=True, Transpose:=False
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I would prefer that the copy command would evaluate only the cells whose
gt; gt; gt; gt; gt; formula calculates a value. By this I mean: Many of the cells in the F5 to
gt; gt; gt; gt; gt; G200 range do not have a calculated value. Assuming that only 5 rows have
gt; gt; gt; gt; gt; values, the copy command will copy the calculated range F5 to G200, meaning
gt; gt; gt; gt; gt; that 195 two-column cell combinations will be pasted to the receiving range
gt; gt; gt; gt; gt; wasting space on Sheet2.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; My macros then copy another range from Sheet3 beginning at row 201. (Again,
gt; gt; gt; gt; gt; there are 195 essentially wasted rows between the cells pasted from Sheet1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have attempted to Copy using VisibleCells and/or Paste-special SkipBlanks
gt; gt; gt; gt; gt; = True - yet I still get the 195 quot;Wasted-spacequot; cells.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; It seems that there are three solutions:
gt; gt; gt; gt; gt; One is a smarter way to copy only the cells that the
gt; gt; gt; gt; gt; underlying formula calculates a value.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; -or-
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Another, is to Paste only those cells from Sheet1 which
gt; gt; gt; gt; gt; have values (text or numeric)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; -or-
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Develope a routine to delete the empty rows on Sheet2.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Which of the three is the smartest/most efficient?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I need help with coding the syntax in VBA.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks, Dennis
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Dave,

Thanks again for your help and insight.

I made a change in the first lin of the quot;IFquot; Statement as testing for quot;quot;
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 = quot;quot; Then
'do nothing
Else
Do
If Len(BotCell.Offset(0, -1).Value) gt; 0 Then
Set BotCell = BotCell.Offset(1, 0)
Else
Exit Do
End If
Loop
Set MyRngToCopy = .Range(TopCell, BotCell).Resize(, 2)
End Ifquot;Dave Petersonquot; wrote:

gt; You can see what's left over if you toggle a setting:
gt; tools|options|transition tab|check Transition navigation keys
gt;
gt; You'll see a single apostrophe in those cells.
gt;
gt; Remember to toggle that setting back to off.
gt;
gt; One way to clean up that mess left over from edit|copy, edit|paste
gt; special|values:
gt;
gt; select all those cells
gt; edit|replace
gt; what: (Leave blank)
gt; with: $$$$$
gt; replace all
gt;
gt; Then do it again
gt; edit|replace
gt; what: $$$$$
gt; with: (leave blank)
gt; replace all
gt;
gt;
gt;
gt; Dennis wrote:
gt; gt;
gt; gt; For others who may read this thread.
gt; gt;
gt; gt; If you Copy/Paste's (values-only), when the source range contains formulas
gt; gt; that set the cell(s) value(s) to quot;quot; (like =IF(ISBLANK(B2),quot;quot;,B2), the
gt; gt; Pasted-to cell range can become part of the used-range (at least as it
gt; gt; relates to functions using .End(xlUp or xlDown or xlRight or xlLeft).
gt; gt;
gt; gt; The receiving cells (of the combined copy/paste procedure) do not contain
gt; gt; any visible values or formulas, but to XL the cells are not the same as
gt; gt; never-used cells.
gt; gt;
gt; gt; **********************************************
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; The cells aren't empty. They contain formulas that evaluate to quot;quot;.
gt; gt; gt;
gt; gt; gt; So that .specialcells() stuff won't help.
gt; gt; gt;
gt; gt; gt; I would think the quickest solution would be just to look down column F looking
gt; gt; gt; for quot;quot;. As soon as you find it, go back up a row and do the copy.
gt; gt; gt;
gt; gt; gt; This may work if your range to copy is contiguous.
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Sub testm()
gt; gt; gt;
gt; gt; gt; Dim TopCell As Range
gt; gt; gt; Dim BotCell As Range
gt; gt; gt; Dim myRngToCopy As Range
gt; gt; gt;
gt; gt; gt; With Worksheets(quot;Pivot tablequot;)
gt; gt; gt; Set myRngToCopy = Nothing
gt; gt; gt; Set TopCell = .Range(quot;F5quot;)
gt; gt; gt; Set BotCell = TopCell
gt; gt; gt; If TopCell.Value = quot;quot; Then
gt; gt; gt; 'do nothing
gt; gt; gt; Else
gt; gt; gt; Do
gt; gt; gt; If BotCell.Offset(1, 0).Value = quot;quot; Then
gt; gt; gt; Exit Do
gt; gt; gt; Else
gt; gt; gt; Set BotCell = BotCell.Offset(1, 0)
gt; gt; gt; End If
gt; gt; gt; Loop
gt; gt; gt; Set myRngToCopy = .Range(TopCell, BotCell).Resize(, 2) '2 columns
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; If myRngToCopy Is Nothing Then
gt; gt; gt; 'nice msgbox
gt; gt; gt; Else
gt; gt; gt; myRngToCopy.Copy
gt; gt; gt; Worksheets(quot;mysheetquot;).Range(quot;b239quot;).PasteSpecial
gt; gt; gt; Paste:=xlPasteValues
gt; gt; gt; End If
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Dennis wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks Dave!
gt; gt; gt; gt;
gt; gt; gt; gt; I inserted your code into my macro as follows.
gt; gt; gt; gt;
gt; gt; gt; gt; With Worksheets(quot;Pivot Tablequot;)
gt; gt; gt; gt; Set MyRngToCopy = Nothing
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; Set MyRngToCopy = .Range(quot;F5:Gquot; amp; .Cells (.Rows.Count, quot;Gquot;)
gt; gt; gt; gt; .End(xlUp).Row).Cells.SpecialCells(xlCellTypeFormu las)
gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt; If MyRngToCopy Is Nothing Then
gt; gt; gt; gt; MsgBox quot;Nothing to Copyquot;
gt; gt; gt; gt; Else
gt; gt; gt; gt; Sheets(MySheet).Select '?? Could this confuse the quot;Selectionquot;
gt; gt; gt; gt; Range(quot;B239quot;).Select '?? Could this confuse the quot;Selectionquot;
gt; gt; gt; gt; MyRngToCopy.Copy
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; gt; gt; End If
gt; gt; gt; gt; End With
gt; gt; gt; gt;
gt; gt; gt; gt; The copy process still inserts quot;phantomquot; cells. The cells to copy from are
gt; gt; gt; gt; formula cells - just with no value. The formula in the cells being copied
gt; gt; gt; gt; from is: =IF(ISBLANK(B2),quot;quot;,B2)
gt; gt; gt; gt;
gt; gt; gt; gt; On the receiving sheet, XL does not see formulas or blanks (in the subject
gt; gt; gt; gt; area) but does see constants (using the GoTo routine).
gt; gt; gt; gt;
gt; gt; gt; gt; Note above my comments about the the prior quot;selections.quot; to the
gt; gt; gt; gt; MyRngToCopy.Copy
gt; gt; gt; gt; process. BTW, I moved those other selections out and in the quot;Withquot; process
gt; gt; gt; gt; with the same result. Do we need possibly ....
gt; gt; gt; gt; SpecialCells(xlCellTypeFormulas, XX)?
gt; gt; gt; gt;
gt; gt; gt; gt; Dennis
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; If you try this by hand, does it select the cells that you want copied? And if
gt; gt; gt; gt; gt; it does, is that range a nice rectangular contiguous block of cells?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Select F5:Gxxx
gt; gt; gt; gt; gt; edit|goto|special
gt; gt; gt; gt; gt; formulas
gt; gt; gt; gt; gt; click ok.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If it does, you could do that same kind of thing in code.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dim myRngToCopy As Range
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; With Worksheets(quot;pivot tablequot;)
gt; gt; gt; gt; gt; Set myRngToCopy = Nothing
gt; gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; gt; Set myRngToCopy = .Range(quot;f5:Gquot; amp; .Cells(.Rows.Count, quot;Gquot;).End(xlUp).Row) _
gt; gt; gt; gt; gt; .Cells.SpecialCells(xlCellTypeFormulas)
gt; gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If myRngToCopy Is Nothing Then
gt; gt; gt; gt; gt; MsgBox quot;nothing to copyquot;
gt; gt; gt; gt; gt; Else
gt; gt; gt; gt; gt; myRngToCopy.Copy
gt; gt; gt; gt; gt; 'later...
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =========
gt; gt; gt; gt; gt; Did you really mean you had formulas in that F5:Gxx range???
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dennis wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Using 2003
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Sheet1: Current macro calculates a range of cells from a fixed upper-left
gt; gt; gt; gt; gt; gt; range cells F5 to G200.: (Note that Sheet1 is a Pivot-table already using
gt; gt; gt; gt; gt; gt; Auto filter.)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Sheets(quot;Pivot Tablequot;).Range(Cells(5, quot;Fquot;).Address, Cells _
gt; gt; gt; gt; gt; gt; (Rows.Count, quot;Gquot;).End(xlUp).Address).Copy
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; (followed by
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues,
gt; gt; gt; gt; gt; gt; Operation:=xlNone, SkipBlanks:=True, Transpose:=False
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I would prefer that the copy command would evaluate only the cells whose
gt; gt; gt; gt; gt; gt; formula calculates a value. By this I mean: Many of the cells in the F5 to
gt; gt; gt; gt; gt; gt; G200 range do not have a calculated value. Assuming that only 5 rows have
gt; gt; gt; gt; gt; gt; values, the copy command will copy the calculated range F5 to G200, meaning
gt; gt; gt; gt; gt; gt; that 195 two-column cell combinations will be pasted to the receiving range
gt; gt; gt; gt; gt; gt; wasting space on Sheet2.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; My macros then copy another range from Sheet3 beginning at row 201. (Again,
gt; gt; gt; gt; gt; gt; there are 195 essentially wasted rows between the cells pasted from Sheet1)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have attempted to Copy using VisibleCells and/or Paste-special SkipBlanks
gt; gt; gt; gt; gt; gt; = True - yet I still get the 195 quot;Wasted-spacequot; cells.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; It seems that there are three solutions:
gt; gt; gt; gt; gt; gt; One is a smarter way to copy only the cells that the
gt; gt; gt; gt; gt; gt; underlying formula calculates a value.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; -or-
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Another, is to Paste only those cells from Sheet1 which
gt; gt; gt; gt; gt; gt; have values (text or numeric)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; -or-
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Develope a routine to delete the empty rows on Sheet2.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Which of the three is the smartest/most efficient?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I need help with coding the syntax in VBA.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks, Dennis
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

VBA treats empty cells just like excel. But formulas that evaluate to quot;quot;
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 quot;quot;.

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

You are correct that many of the cells in the computed range evaluate to quot;quot;.
The cells that I wanted were those which did not = quot;quot;. 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 amp; 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 quot;To Copyquot; range
since the data stopped usually far less than 20,000 cells down.

Your information was crutial 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() gt;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 quot;screen.quot;

Thanks!

Yesterday, after

quot;Dave Petersonquot; wrote:

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