Paste special minus blanks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dzelnio - 15 Jun 2007 03:05 GMT

While linking workbooks, I used paste special to link columns A:D from
workbook 1 to the same columns on workbook 2.

Works great except it transfers blank cels as zeros and screws up my
macro. How can I link without transferring blank rows?

Reply to this Message

Dave Peterson - 15 Jun 2007 04:01 GMT

When you do pastespecial|link, you end up with a formula like:

=sheet2!a1

You can change that formula to:

=if(sheet2!a1="","",sheet2!a1)

So that the cell looks empty when the sending cell is empty.

> While linking workbooks, I used paste special to link columns A:D from
> workbook 1 to the same columns on workbook 2.
>
> Works great except it transfers blank cels as zeros and screws up my
> macro. How can I link without transferring blank rows?

Signature

Dave Peterson

Reply to this Message

dzelnio - 15 Jun 2007 12:16 GMT

I changed that in cel A1 but nothing else was affected.

Where exactly should I change the formula?

dzelnio

> When you do pastespecial|link, you end up with a formula like:
>
[quoted text clipped - 17 lines]
>
> Dave Peterson

Reply to this Message

Dave Peterson - 15 Jun 2007 12:29 GMT

Every cell that got the original formula will have to change.

> I changed that in cel A1 but nothing else was affected.
>
[quoted text clipped - 23 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

dzelnio - 15 Jun 2007 13:14 GMT

Is there a quick way to modify all the cells in Columns A:D to work
this way?

> Every cell that got the original formula will have to change.
>
[quoted text clipped - 31 lines]
>
> Dave Peterson

Reply to this Message

Dave Peterson - 15 Jun 2007 15:53 GMT

First, I wouldn't use all of column A:D -- there's 64k rows in xl97-xl2003 and
1MB rows in xl2007.

I'd bet that this would be a lot of formulas that wouldn't be used and would
just add overhead to the workbook.

Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim DestCell As Range

With Worksheets("sheet2")
Set RngToCopy = .Range("a1:D999") ' A:D if you really, really want!
End With

With Worksheets("sheet1")
Set DestCell = .Range("a1")
End With

With RngToCopy
DestCell.Resize(.Rows.Count, .Columns.Count).Formula _
= "=if(" & .Cells(1).Address(rowabsolute:=False, _
columnabsolute:=False, external:=True) & "=""""," & _
"""""," & .Cells(1).Address(rowabsolute:=False, _
columnabsolute:=False, external:=True) &
")"
End With

End Sub

> Is there a quick way to modify all the cells in Columns A:D to work
> this way?
[quoted text clipped - 34 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

dzelnio - 15 Jun 2007 19:34 GMT

Forgive my rookie-ness. Is this a macro? How do I use it?

Dave

> First, I wouldn't use all of column A:D -- there's 64k rows in xl97-xl2003 and
> 1MB rows in xl2007.
[quoted text clipped - 70 lines]
>
> Dave Peterson

Reply to this Message

Dave Peterson - 15 Jun 2007 20:50 GMT

Yep, it's a macro.

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to do it manually...

Select the range to receive the formulas (say A1:D999)
With A1 the activecell, type this formula:

=if('sheet2'!a1="","",'sheet2'!a1)
(adjust the sheetname to match your requirements)

But instead of hitting enter, hit ctrl-enter to fill the selection with that
formula (excel will adjust it for the other cells).

> Forgive my rookie-ness. Is this a macro? How do I use it?
>
[quoted text clipped - 74 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

dzelnio - 15 Jun 2007 22:15 GMT

I did it manually. It was touchy, but I got it to work.
Thanks so much!
Dave

> Yep, it's a macro.
>
[quoted text clipped - 95 lines]
>
> Dave Peterson

Reply to this Message