Resize and Move to Fit

Pestomania

Active Member
Joined
May 30, 2018
Messages
292
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I'm not sure if this is even possible but it would be awesome!!

Is there a vba code to select all pictures and textboxes *only* and rearrange, resize to make all of them fit on the Excel sheet without overlay of each other or overlay of text in column A?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
This does what you asked for, but is unlikely to yield exact desired result without amening to match your own requirements
Test on a COPY of your workbook!
Code:
Sub reArrange()
    Dim Shp As Shape, L As Double, T As Double
    With ActiveSheet
        L = .Range("A1").Left + .Range("A1").Width
        T = .Range("A1").Top
        For Each Shp In .Shapes
            With Shp
                .Top = T
                .Left = L
                .Width = 100
                L = L + 100
            End With
        Next Shp
    End With
End Sub
Let us know if you need further help - need more details if you want to control where the objects are placed
 
Upvote 0
This does what you asked for, but is unlikely to yield exact desired result without amening to match your own requirements
Test on a COPY of your workbook!
Code:
Sub reArrange()
    Dim Shp As Shape, L As Double, T As Double
    With ActiveSheet
        L = .Range("A1").Left + .Range("A1").Width
        T = .Range("A1").Top
        For Each Shp In .Shapes
            With Shp
                .Top = T
                .Left = L
                .Width = 100
                L = L + 100
            End With
        Next Shp
    End With
End Sub
Let us know if you need further help - need more details if you want to control where the objects are placed

Hi Yongle! Wow thank you so much! I am going to test this immediately when I get to work tomorrow. The only thing I would like to look at is if there is a way to determine which row the last text in column A is and have it fill space below that, but place nothing above that?
 
Upvote 0
The only thing I would like to look at is if there is a way to determine which row the last text in column A is and have it fill space below that, but place nothing above that?
For simplicity the code moves ALL shapes (not only textboxes and pictures)
- are there any other shapes on the sheet?

Code:
Sub reArrange()
    Dim Shp As Shape, L As Double, T As Double
    With ActiveSheet
        With .Range("A" & Rows.Count).End(xlUp).Offset(1)
            L = .Left + .Width
            T = .Top
        End With
        For Each Shp In .Shapes
            With Shp
                .Top = T
                .Left = L
                .Width = 100
                L = L + 100
            End With
        Next Shp
    End With
End Sub
 
Last edited:
Upvote 0
For simplicity the code moves ALL shapes (not only textboxes and pictures)
- are there any other shapes on the sheet?

Code:
Sub reArrange()
    Dim Shp As Shape, L As Double, T As Double
    With ActiveSheet
        With .Range("A" & Rows.Count).End(xlUp).Offset(1)
            L = .Left + .Width
            T = .Top
        End With
        For Each Shp In .Shapes
            With Shp
                .Top = T
                .Left = L
                .Width = 100
                L = L + 100
            End With
        Next Shp
    End With
End Sub

Hi, I am okay with it moving everything. I would personally love to see it determine where the last used row in column "A" is and fill the space below with white and rearrange the images on top of that.

But really, I just don't want it to cover anything that has column "A" filled.
 
Upvote 0
If you want the first shape in column A, then when L is first calculated, amend the code in post#4 to
Code:
L = .Left
(ie do not add the width of the first column into the calculation)
 
Upvote 0
If you want the first shape in column A, then when L is first calculated, amend the code in post#4 to
Code:
L = .Left
(ie do not add the width of the first column into the calculation)

Hi. I'm sorry. I am okay with the image starting in any column. There will be text in column "A". The images should not cover anything that is in column A. So it would need to determine what the last row in column A is (ie. Row 10 is the last used), so starting in row 11, start to fill.

Also, the bottom of the page in excel is row 22, is there a way to say not to go further than that?
 
Upvote 0
after teating the code, let me know what's wanted
 
Upvote 0
after teating the code, let me know what's wanted

Hi Yongle. I have tested the code. It places everything side by side which works for me, but the images are tiny now. I placed large images and now they are 1.2" tall.

And it works perfect for the rows.count! Only thing is that I would need it to stop the height at the print area if possible.

Thank you. You are amazing!
 
Upvote 0
but the images are tiny now
You said you wanted to resize them and this is the line that is doing it
Code:
.Width = 100

How do you want to resize?
- are images to be treated same as textboxes etc?
Are all images named "Picture " followed by a number?
Are all TextBoxes named "TextBox" followed by a number?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,657
Messages
6,120,777
Members
448,991
Latest member
Hanakoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top