Organize Layout of All Charts

[deZine]

Hello,
I was given VBA that reorganizes all the charts on a sheet by a certain number of charts wide. I would like to change it to be a particular number of charts tall instead.

For Example in the code below is set to be 7 charts wide, so if I have 14 charts, the grid will be 7 charts wide by 2 charts tall. It lays each one to the right of the previous chart until it reaches the "nColumns" then starts the next row of charts. This results in the charts being further away from their source column than I would like.
Right now it is laying them out in this order:
1 2 3 4 5 6 7
8 9 10 11 12 13 14

I would like it to set them up with the height defined as 3 tall and lay them out top to bottom until that number is reached and then start the next column of charts.
I am hoping to have it laid out in this order:
1 4 7 10 13
2 5 8 11 14
3 6 9 12

'Change size and position of all charts
    Dim iChart As Long
    Dim nCharts As Long
    Dim dTop As Double
    Dim dLeft As Double
    Dim dHeight As Double
    Dim dWidth As Double
    Dim nColumns As Long

    dTop = 55      ' top of first row of charts
    dLeft = 212    ' left of first column of charts
    dHeight = 202  ' height of all charts
    dWidth = 431   ' width of all charts
    nColumns = 7   ' number of columns of charts
    nCharts = ActiveSheet.ChartObjects.Count

    For iChart = 1 To nCharts
        With ActiveSheet.ChartObjects(iChart)
            .Height = dHeight
            .Width = dWidth
            .Top = dTop + Int((iChart - 1) / nColumns) * dHeight
            .Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
        End With
    Next

Thanks so much for taking the time to read this and for any help!

 

[Domenic]

Try . . .

'Change size and position of all charts
    Dim iChart As Long
    Dim nCharts As Long
    Dim dTop As Double
    Dim dLeft As Double
    Dim dHeight As Double
    Dim dWidth As Double
    Dim nRows As Long

    dTop = 55      ' top of first row of charts
    dLeft = 212    ' left of first column of charts
    dHeight = 202  ' height of all charts
    dWidth = 431   ' width of all charts
    nRows = 3   ' number of rows of charts
    nCharts = ActiveSheet.ChartObjects.Count

    For iChart = 1 To nCharts
        With ActiveSheet.ChartObjects(iChart)
            .Height = dHeight
            .Width = dWidth
            .Top = dTop + ((iChart - 1) Mod nRows) * dHeight
            .Left = dLeft + Int((iChart - 1) / nRows) * dWidth
        End With
    Next

Hope this helps!

 

[deZine]

Hi Dominic,
Thanks so much for the rapid response and apologies for my delay in messaging back. That works exactly as I hoped. Thanks again and Happy holidays!

 

[Domenic]

That's great to hear, I'm glad I could help.

And Happy Holidays to you too as well.

Cheers!