Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Dennis
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc values

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
Dennis
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dennis
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
Dennis
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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
  #8   Report Post  
Posted to microsoft.public.excel.misc
Dennis
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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

  #9   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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
  #10   Report Post  
Posted to microsoft.public.excel.misc
Dennis
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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 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



  #11   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Copy/Paste how to avoid the copy of formula cells w/o calc val

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 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I fix values in cells calculated by formula in Excel Leigh Excel Worksheet Functions 1 January 19th 06 08:36 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
If formula that looks at multiple cells and values, and then calul Adam Excel Discussion (Misc queries) 1 June 5th 05 11:31 AM
How to copy cells with keeping exact formula intact Stephen Excel Discussion (Misc queries) 6 April 3rd 05 10:56 PM
How do you copy a cell's content verses it's formula? Tammy Excel Discussion (Misc queries) 1 March 2nd 05 07:30 PM


All times are GMT +1. The time now is 09:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"