Paste special minus blanks

  • Thread starter Thread starter dzelnio
  • Start date Start date
D

dzelnio

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?
 
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.
 
I changed that in cel A1 but nothing else was affected.

Where exactly should I change the formula?

dzelnio
 
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
 
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).
 
Back
Top