Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Unknown number of dynamic arrays - how to?

  1. #1
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    502
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Unknown number of dynamic arrays - how to?

    I've put together a sub that formats the page setup aspect of my sheets. However, there are instances where it falls apart, such as when a table (long & narrow) and a pivot table (or three) (short & wide) occupy the same sheet; in this case I wind up with ridiculously small print (all columns on single page)

    Thanks to Ejaz (UsedAreas), I'm able to divide each area up, thus making a separate print area for each entity on the worksheet. However, when the sub is done executing, only the last area evaluated has a valid/visible print area.

    VonPookie has a nice solution to storing each print area in an array (here) but my issue is that I won't always know how many print areas I'm going to need (I could have a single table/range on a worksheet or 5 tables on the same sheet). I'd like the sub to automatically tally the number of used areas (qty: x; derived from Ejaz's function), create x number of dynamic arrays, and fill each array with a separate print area. Is this possible??

    I'm not wanting to print each area each time I run the sub, but I don't know if Excel has any native ability to store in a workbook with multiple print areas (ie, I run the sub, save the workbook, come back later and the print areas are preserved without having to re-run the sub). Feedback/place to start would be welcome.

    My work so far; but I'm stumped as to how to introduce a variable number of dynamic arrays:
    Code:
      Set UsedAreasRange = rng_UsedAreas(rngUsed)
          For Each EachArea In UsedAreasRange.Areas
            With obj.PageSetup
              .PrintArea = EachArea.address   ' Define Print Area to actual UsedArea || http://www.mrexcel.com/forum/excel-questions/298884-visual-basic-applications-limit-printed-area.html
              
              ' ~~ If sht less than 75 rows & 26 columns, then print to single page; else fit wide only
              If EachArea.Rows.Count <= 75 And EachArea.Columns.Count <= 26 Then
                .FitToPagesWide = 1
                .FitToPagesTall = 1
              Else
                .FitToPagesWide = 1
                .FitToPagesTall = False
              End If
    
              ' ~~ Automatically set .Orientation based on height/width ratio
              If (EachArea.Width / EachArea.Height) > 0.85 Then
                .Orientation = xlLandscape
              Else
                .Orientation = xlPortrait
              End If
            End With
          Next EachArea
    I know Hui put together his awesome tool, but I'm looking for something a little more simple (if possible).

    Thanks y'all.

  2. #2
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    502
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unknown number of dynamic arrays - how to?

    Thoughts anyone? My only thought would be to run the UsedAreas twice -- once to count the number of areas on a sheet; and a second time to actually find the ranges for each UsedArea. Also, since I don't always want to print immediately, storing the ranges on Hui's sheet is probably my best bet, barring anyone's secret knowledge of Excel's ability to store multiple print areas native.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •