Merge with blank cell below, loop

[Kertch]

Hi there, I need a macro that will merge a cell with the empty cell below, then move to the next non-blank cell in the column and repeat. My row count will vary.

For purposes of illustration, let's say the data I want to merge is in column A. I have data in A1, A3, A5, A7...and blanks in A2, A4, A6, A8...and so on. There will always be alternating data cells and blank cells like this. Column B has no empty cells through the last row so it can be used to validate the last row. I want to merge A1 with A2, A3 with A4, and so on.

 

[Greg Truby]

So what specific portion of the code that you have written is giving you problems?

 

[Kertch]

Well, I'm a beginner with VBA so all I have so far is the Do command loop to check if the cell in Column B is blank in which case the loop exits. I don't have any idea how to write a code that will determine if there is data in Column A, merge it with the blank cell below if there is data, and then move on to the next data cell. I've looked around through this forum but haven't found anything that works for me.

 

[Greg Truby]

Before we get to the actually coding, let me ask why you are wanting to merge A1 and A2? I ask because as a general rule merged cells cause more problems then they solve. Most of your more advanced users will avoid merged cells like the plague. Perhaps we solve whatever problem is prompting the merging of the cells through a means that will not require such poor design.

 

[Kertch]

The merged cell is based on an end user requirement for this report. The data cells in column A need to wrap due to length and all of the contents must be visible - most data cells contain 2 lines-worth of data but some contain just 1. If I simply wrap and auto-size, the 1st line of the 2-row set is larger than the blank cell line for those affected by the wrap. The end user wants a consistent look. I've also tried just making all of the row heights equivalent to 2 rows, but it makes the report too many pages (or too small to read if shrunk to fit). My best option is to wrap the data cell and then merge it with the cell below.

It sounds like it might be easier to manually merge the cells ~ I can handle that one step as everything else is successfully automated.

 

[Greg Truby]

You might try using the justify command. On the menu it's (for XL2003) Edit | Fill | Justify... and see if that will do the trick. In code it would look like so:

Range("A1:A2").Justify

I would play with that concept first and see if that will work for the user before I would merge cells. If that doesn't work go ahead and use the macro recorder to get started on how to merge cells in code and take a crack at writing your loop and post back if you get stuck.

Again, it's not that you can't put a toilet in the middle of the dining room or merge cells in Excel. It's just that most folks consider both about equally poor design.

 

[VoG]

Again, it's not that you can't put a toilet in the middle of the dining room or merge cells in Excel. It's just that most folks consider both about equally poor design.

Brilliant

 

[skittles96]

You might try using the justify command. On the menu it's (for XL2003) Edit | Fill | Justify... and see if that will do the trick. In code it would look like so:

Range("A1:A2").Justify

I would play with that concept first and see if that will work for the user before I would merge cells. If that doesn't work go ahead and use the macro recorder to get started on how to merge cells in code and take a crack at writing your loop and post back if you get stuck.

What is the equivalent of Justify in XL2007 ?

 

[VoG]

What is the equivalent of Justify in XL2007 ?

It is exactly the same.