1

I have a table filled with datas that comes from other tables. The formulas in the cells of my table are quite simple (reference to a cell in another worksheet).

Table example

In my table, I have "Title" rows (rows 57 and 64 in that case) that contains only one text value that I would like to overflow to adjacent cells since the adjacent cells in those rows will always be empty.

If the values in the table where entered manually, there would be no problem and the cell would looks like exactly like I want it as in the image below.

Table has it would be if data was entered manually and has I want it

The value of the first cell in row 57 does not overflow because the adjacent cell containt a formula (the formula is basically "=OtherCell" where OtherCell does not contain any value (more specifically, a value of ""))

It is important to note that the position of the "title" row changes and occurs in many rows in the table since the data are automatically extracted a database.

How can I achieve this...

  • Without using manual intervention (those are automated reports, I can't delete formulas because the next report generated will be erroneous)
  • VBA can be used, but not in a way that modify the table in an "irreversible" way (in a way that makes that the next time the sheet is calculated, the data is erroneous), for the same reason as manual intervention.

I use Excel 2013, but I believe this question applies to 2007+ versions of Excel.

  • You could set column A to wrap text? Sure, you'll get multiple lines, but it's a trade-off between readability and uniform display. – Andi Mohr May 23 '17 at 10:39
  • I often have long titles, which would make the table look quite strange, so it is not a very good option. – Gabriel Caron L'Écuyer May 23 '17 at 10:51
  • Maybe you could write a VBA macro that would delete all formulas that yield an empty cell. – Jérôme Radix Jul 4 at 9:49
2

The short answer is: You can't.

The long answer is that only truly empty cells will allow text from the next cell to the left to spill over and display in that cell.

This is just how Excel works.

If you specifically exclude manual intervention, deleting formulas, as well as VBA, there's really nothing left other than accepting the defaults, which is that a formula in a cell to the right will prevent text from spilling over to the right.

Don't shoot the messenger.

  • I know about Excel's behaviour with cell's overflow as very well explained by @DanHenderson in this post : link. My question is more if someone knows of a workaround since it is something that has bothered me for years. VBA can be used, but not to delete formulas. – Gabriel Caron L'Écuyer May 23 '17 at 10:43
  • Or, more specifically, VBA can be used, but not in a way that modify the table in an "irreversible" way (in a way that makes that the next time the sheet is calculated, the data is erroneous). – Gabriel Caron L'Écuyer May 23 '17 at 10:54
  • Well, no, not with these restrictions. Maybe you want to post a suggestion on Excel.Uservoice.com. If it gets a lot of votes, it may be one of the next features the Excel team will work on. – teylyn May 23 '17 at 11:08
0

Using VBA, you can void cells containing a formula that yield nothing :

Private Sub CommandButton21_Click()
    For Each c In Worksheets("yourworksheet").Range("yourrange")
        If c.Text = "" Then c.Select: Selection.ClearContents
    Next
End Sub

Thus, adjacent cells can extend over empty cells.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.