Determining total column width of visible cells

[alexramo]

Hello All,
Thank you for your help on past posts, your information was very helpful so I thought I would try again with a new challenge.

I am trying to resize a picture on a worksheet to fit a dynamic measurement(the size of the page is different depending on the contents in cells).
I am doing this all in VBA.

so what I have come up with is that I need to
1) determine the total column width of only the non-hidden(visible) cells.
2) resize my picture to those measurements, so I can get an exact fit, and it doesn't create a new page break, by going over.

I have found some code on forums, and am thinking of something like this as a solution(although I don't know how to complete it):

For summing column widths:



Code:
Function Sum_Visible_Columns(Columns_To_Sum As Object)

Application.Volatile
For Each Column In Columns_To_Sum
If Columns.Hidden = False Then
total = total + cell.Width

End If
End Function-or-



Code:
Dim rng1 As Range
Set rng1 = rng.SpecialCells(xlVisible)

then I would have to use that total column width number as either inches or pixels to resize the width and height to fit on one page of the activesheet.


for that I was thinking of something like this:


Code:
Dim shp As Shape


shp.LockAspectRatio = msoFalse
shp.Height = x(total column width times something????)
shp.Width = y(the total column width)
I appreciate everyones help, thank you,
-alex

 

[SkipVought]

Hi,

How about

TotWid = activesheet.usedrange.specialcells(xlcelltypevisible).columns.width

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alexramo]

Thank you, that does seem to give me a value.
now one more quick question,
how can I put it into


code:
ActiveSheet.Shapes("Picture 1").Select
Selection.ShapeRange.ScaleWidth (totwid / ?), msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight (totwid / ?), msoFalse, msoScaleFromTopLeft

to get it to fit properly?
is there a conversion number or do I have to put a conversion formula into the macro?

thank you again.

 

[SkipVought]

? depends on how you want to scale.

Here's how I would approch...

With ActiveSheet.Shapes("Picture 1")
  .Width = totwid
  .Height = ???
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alexramo]

Thanks again.
It actually happened to shrink my picture.
I guess I was under the impression that if I was able to calculate the width of the visibe cells, and set the width of the picture to it,it would fill up the whole page.

What have I assumed wrong here? is the picture being measured in a different scale than what your formula is returning?

once again, thank you for your time.

 

[alexramo]

also does

code:
range.width

work, or does it take into account the hidden columns?

 

[SkipVought]

It is making the WIDTH of the shape the same width as the used range.

Try this...

With ActiveSheet.Shapes(1)
    .Top = 0
    .Left = 0
    .Width = ActiveWindow.VisibleRange.SpecialCells(xlCellTypeVisible).Columns.Width
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Alex,

Why do you need vba for this? It seems to me that selecting the picture, making it the size you want initially, then using Format|Picture|Properties and checking the 'move and size with cells' option would achieve what you're after.

Cheers

[MS MVP - Word]

 

[alexramo]

I'll give that a try as well, thank you.
However when the picture is pasted into the excel sheet, it is not always the same size, and the column widths are dynamic as well.
Skip Vought's solution seems to be working well.