Automatic Row Height with Pictures

I'm not great with VBA, but I think it's the only way to solve this puzzle.

I have a table with one picture in a particular cell that will move around. (It's a linked picture to a table, since you can't nest tables. -___- )

What I would like is this: A macro/VBA code that will BOTH expand a row's height if the wrapped text drops to the next line/s, and will also keep the row height of the picture the same upon table sorts.

I found some code for the row height bit:

Please Login or Register  to view this content.

But this doesn't help me with the picture. It resets the picture row height to 1 line high each time I sort.

I hope I made this clear enough. Thanks for any help!

Hi uvuriel03

I think a little bit more clarification would help.

You don't mention text, until you are trying to adjust the row height of some text, and then you seem to be adjusting the column width. Could you perhaps expand a little on what you are trying to do, and to what you are trying to do it?

I'm intrigued by the "picture of a table" - that would also benefit from some explanation!)

Tony

Hi Tony,

I didn't mention column width at all. If you're talking about the VBA I provided, I think it is autofitting the height of selected columns (which is all of them). It doesn't change the width at all. Like I said, I'm not good with VBA, this is just something I found.

As to the linked picture table, see here.

I have an Excel spreadsheet with many rows of differing heights. One of these rows contains a picture of a range that's like 6 rows high. I would like the rows to auto-adjust to the proper height of the wrapped text of any text entered by a user. I would also like the rows to retain that height upon sorting and/or filtering.

Well, here's Microsoft on the topic:

AutoFit Method

Changes the width of the columns in the specified range to achieve the best fit.

expression.AutoFit

expression Required. An expression that returns a Range object. Must be a row or a range of rows, or a column or a range of columns. Otherwise, this method causes an error.

Remarks
One unit of column width is equal to the width of one character in the Normal style.

Example
This example changes the width of columns A through I on the datasheet to achieve the best fit.


myChart.Application.DataSheet.Columns("A:I").AutoFit

OK - I think I see what the problem is that you are facing. We need to think about just how a picture is copied into a spreadsheet. I've attached a small spreadsheet from some other work I was doing on the forum, and you will see on sheet Codes, that there is a picture of a range from another sheet (DesiredResults) (at about cell G3) and also an actual copy of the same range, at cell D3.

Now, using your keyboard only, position the cursor in cell A1.

Use the Right-Arrow key to skip from A1 to H1.
Now use the Down-Arrow key to go down to H3.

Now - here is where you have to be really attentive.
Use the lLeft Arrow key and slowly move left, through H3, G3, F3E3, D3 and so on.

Did you notice that when you were on cells D3 and E3, which was the 'plain paste' action, the values of the cells showed up, but when you were over cells G3 and H3, nothing showed up in the little area above cell A1, except the words Picture 3.

Let's try it again. Using your mouse select cell E3. There is a hard black border around the cell, indicating that it has been selected.
Do the same thing for cell G3. Nothing gets a border, but the Picture is selected with all of its handles, and the rotation handle.

The conclusion from this is:

The picture is not located in a cell

It is in fact in a completely separate layer 'floating' above the array of cells. Now let's sort the worksheet.
Select cells A1 thru H8, and go to the Sort function. Select 'My Data has Headers', and sort on the Tag field.

The picture has not moved (it's not in the layer that got sorted, but the cells have been sorted, and there is now a gap in cells D4 and E4. Notice that the picture is totally unaffected by the sort operation.

So, you assertion that the picture is 'in' the cell is not valid. I'd be interested to see how you make the picture move around.

Can you tell me a little more about how you got the picture where you want it - or even better, attach a copy of the spreadsheet?

HTH

Tony

Oops!

This time with the attachment!