Resize and Move to Fit

Pestomania

Board Regular
Joined
May 30, 2018
Messages
160
Office Version
  1. 2013
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

Pestomania

Board Regular
Joined
May 30, 2018
Messages
160
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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:

Pestomania

Board Regular
Joined
May 30, 2018
Messages
160
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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)
 

Pestomania

Board Regular
Joined
May 30, 2018
Messages
160
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
after teating the code, let me know what's wanted
 

Pestomania

Board Regular
Joined
May 30, 2018
Messages
160
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,003
Messages
5,834,825
Members
430,324
Latest member
bosphoruskid

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
Top