Getting text to overlap into other cells

  • Thread starter Thread starter Maury Markowitz
  • Start date Start date
M

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
 
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.
 
The formulas in column B mean that the cell isn't blank even if the formula
result is "".
Click to expand...

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