macro to resize shape to size of rows with text in it

[tonywatsonhelp]

Hi Everyone,

I have a shape Called "View1"

all i want is a macro that will increase or reduce its size according to the data in the column it surrounds

So the shape is placed around data in column C (the width if fine,

My rows are all set at height of 15
Data starts in Row 16

so i want a macro that checks how many rows in coulmn C16 down have data in them and increase the shape by that many rows or reduce it
so lets say shapes default height is 40, then if each row increases the size by 15 then it 40 + 15*number of rows

please help if you can

Thanks
Tony

 

[Tom Urtis]

Strictly speaking, what you need is a worksheet level event procedure, not a macro.

You need to explain a couple items for someone to offer a solution to fit your workbook design.
(1)
How does the data from cell C16 downward get into those cells. Formulas in column C? Manual entry? Copy and paste? Imported from an external source?
(2)
Are cells in that column multi-edited, meaning is only one cell at a time changed, or can many cells in that range be changed.
(3)
Are any cells merged.
(4)
Are any cells formatted for wrapped text.

Keep in mind that no one reading this except you has ever seen your workbook. You did well to specify where the range of interest starts in cell C16 and where theoretically the range would end. Just describe a bit more about what you are working with, with those 4 items in mind, at least as a start to get a reply you can use.