Getting text to overlap into other cells

[Maury Markowitz]

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

 

[Sam Wilson]

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]

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

 

[Maury Markowitz]

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

 

[Maury Markowitz]

Well for what it's worth...

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

did not fix the problem. :-(

Maury

 

[Sam Wilson]

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

 

[Dan C]

For those of us who are trying to do exactly what you are trying to do (let values of previous cells overlap cells with formulas that result in "") but don't know much of anything about creating macros, would you be willing to give some details about how you set this up? I've tried using what you said below and am failing miserably. thanks

 

[Dan C]

Pardon me if this is here twice but it shows I never posted this. I am not good at creating macros (don't know the language but have successfully done a couple with good instructions from others). I am trying to do exactly what is explained below. that is, I need to have a cell overlap the next cells in line when the next cells in line result in "" (from a formula). Can you give explicit instructions on how to create what you are explaining below? thanks

 

[Bob Phillips]

Dan,

The bit explained below isn't, i.e. we have no idea what you want that macro
to do.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)