One of the nice things about xl is that text in a cell will overlap
into the next cell if there's nothing there, rather than be "cut off".
Unfortunately in my spreadsheet this isn't working...

The spreadsheet in question is generated in code. The code first
builds a huge table of data from SQL, and pastes it into columns
starting at BA, which it then hides. The columns the user sees, A
through T currently, are then built by inserting formulas that copy
the data. To save recalc time I then copy and pastevalues any of the
columns that cannot change, which is about half of them. I use
formulas instead of copying and pasting them directly because there's
a small bit of logic that has to be applied to each row, and it SEEMS
that a formula is much faster than looping over them.

Anyway, the A column is sparsely populated text, some of which is
long. If there is text in that column, the formula means there is no
text in B. Yet the text in A is NOT overlapping the cell boundary into
B.

The formulas in column B mean that the cell isn't blank even if the formula
result is "".

You might have to run a loop on column B with the code:

if range("Bx").value = "" then range("Bx").value = ""

Which I know seems like it won't do anything but it will actually remove the
formulas where they return blanks.


"Maury Markowitz" wrote:

One of the nice things about xl is that text in a cell will overlap
into the next cell if there's nothing there, rather than be "cut off".
Unfortunately in my spreadsheet this isn't working...

The spreadsheet in question is generated in code. The code first
builds a huge table of data from SQL, and pastes it into columns
starting at BA, which it then hides. The columns the user sees, A
through T currently, are then built by inserting formulas that copy
the data. To save recalc time I then copy and pastevalues any of the
columns that cannot change, which is about half of them. I use
formulas instead of copying and pasting them directly because there's
a small bit of logic that has to be applied to each row, and it SEEMS
that a formula is much faster than looping over them.

Anyway, the A column is sparsely populated text, some of which is
long. If there is text in that column, the formula means there is no
text in B. Yet the text in A is NOT overlapping the cell boundary into
B.

Any ideas why?

Maury

On Jun 16, 11:02*am, Sam Wilson
wrote:
The formulas in column B mean that the cell isn't blank even if the formula
result is "".

But I pastevalues over the columns, and there's nothing in the empty
ones. It's not that they are blank, they really are empty. Or at least
the empty string, "".

Maury

On further reflection I'm not sure what I said is different than what
you said. So let me get specific.

First I do this...

ActiveSheet.Range("T5:T" & lastRow).formula =
"=IF(ET5=1,EP5+ES5,"""")"

then I do this...

ActiveSheet.Range("T5:T" & lastRow).Copy
ActiveSheet.Range("T5:T" & lastRow).PasteSpecial xlPasteValues

So in this case, is there anything left in the cell?

Maury

Well for what it's worth...

ast.Range("A5:T" & lastRow).PasteSpecial xlPasteValues,
SkipBlanks:=True

did not fix the problem. :-(

Maury

You need to explicitly tell excel that the cell value is "", so you need to
use something like

for i = 0 to 999
if Range("b1").offset(i,0).value = "" then Range("b1").offset(i,0).value =
""
next i

which is different to pasting values over all cells.

I've tried it by puting "aaaaaaaaaaaaaaaaaaaaaaaaaa" in A1, and ="" in B1
which won't let the text from A1 spill over, but after running the above
macro it does.

Sam

"Maury Markowitz" wrote:

Well for what it's worth...

ast.Range("A5:T" & lastRow).PasteSpecial xlPasteValues,
SkipBlanks:=True

did not fix the problem. :-(

Maury