How do I create an excel macro that changes the rows spanned by a cell

Question

I am pasting a table into Excel from SAS where the number of rows spanned by the cell in the first column is variable. That is to say sometimes the first column is 3 merged cells, sometimes it is 6, etc. I would like to run a macro that changes the height of the first cell to be 10 merged cells, and to insert rows at the bottom to make space for this.

I'm a little stumped at how to approach this problem. Any assistance would be most appreciated.

Let me try to illustrate. If I wanted the first column to all span 4 rows, I might start with

_________________
      |__________
______|__________
      |__________
      |__________
______|__________

and end up with

_________________
      |__________
      |__________
      |__________
______|__________
      |__________
      |__________
      |__________
______|__________

Answer 1

Thanks for your explanation. You just made that über tough. The problem is that although it seems you're just changing the height of your merged cell to be larger, you're actually creating a new merge in order to make the newly merged cell 4 cells high.

In your first illustration, you show two merged cells with address ranges of A1:A2 and A3:A6. In your second illustration, the addresses are A1:A4 and A5:A8, respectively.

In order for Excel to "change the height" (I use that phrase loosely) of the original merged cells at address A1:A2, it actually has to create a new set up merged cells at address range A1:A4. However, you won't be able to do that because address A3:A6 is already a merged range and these would overlap.

If you were to try to do this manually, you know that you would have to unmerge the lower group, remerge the top group, and the remerge the bottom group.

You could write a macro that, when executed, popped up an input box that asked the number of cells high the merged cell should be. However, your code would then have to analyze your original stack of merged cells and then unmerge and remerge everything programatically to make the change. This is certainly possible, but it could be a real pain (but also a fun challenge :).

I would ask yourself how badly you want to be able to exactly what you're looking for, because it's not exactly natural behavior in Excel.

---

My original post is below: now obsolete since I now understand what the author is asking

If you just want to change the height of the cells, you can use something like:

    Rows("1:1").RowHeight = 27.75

However, I'm confused by your reference to merged cells. Are you just trying to change the height of a cell or are you also trying to merge cells together?

Answer 2

Whatever you are trying to do, you could select Tools -> Macro -> Record new macro , then manually perform the operation you need, then stop the macro and edit it in VBA to see the corresponding VBA commands to use in your program.