Ozgrid Excel Newsletter. Excel Newsletter Archives

VISIT OUR SPECIALS PAGE | ADVANCED SEARCH |FREE EXCEL & VBA LIFETIME SUPPORT | FREE DEMO DOWNLOADS

Printing Large amounts of data

This month I thought I would offer up some handy hints on printing larger amounts of data.

The default to print a workbook is to print the Active Sheet. This means the sheet that you are actually working on, so if it is a larger sheet and you have a lot of information, you may end up printing lots of pages you don�t need. In my experience it is better to set up a print job properly and print by Selection.

To do this, highlight the area you want to print and go to the Office Button>Print and then check the Selection option. Always go to Print Preview first to see what you are going to print. If your printing is quite large and doesn�t quite fit on the page, there are a couple of things you can do.

If your data is set up for a portrait page (short edge at the top), you can try flipping to landscape (long edge at the top), select the Page Setup option and then on the Page tab, check the Landscape option, then OK. If this is not what you want to do, you can try adjusting your margins/column widths from within the Print Preview environment. Check the Show Margins option, then place your mouse until you see the cross on the top vertical and bottom horizontal lines to adjust the margins, and the black squares at the top of the page to adjust to your column widths. Note there are two horizontal lines at the top of the page, and two at the bottom. Inside these areas are where your headers and footers live if you use them.

If you want to squash your data all on to one page, you can use the Adjust to: option under Scaling on the Page tab under Page Setup. This will force your data to be all on one page. Note the percentage button will show how much your data has been reduced to fit on one page (you can use the spin button to minimise or maximise your work further). Note here that if you make your data too small, no one will read it. It is good practice not to scale your data down too far, otherwise this may happen. There is also a Fit to: option as well. This allows you to specify how many pages you want to fit your data to, the default is 1 page 1 by 1 page tall, but both can be changed by selecting the spin button with your mouse and going up or down as desired.

If your data is going to look to squashed on the page, another option is to spread it across multiple pages, making either the column or row (or both) headings repeat on each page. To do this you will have to get out of Print Preview as the option is unavailable here. Go to the Page Layout tab and select Titles option under the Page Setup group. If you had two rows (lets say 1 and 2) that you wanted to repeat at the top of every page, you would click in Rows to repeat at top: box and then select rows 1 and 2. The reference $1:$2 will appear, note the dollar symbols mean that this is an absolute reference, meaning that the entire rows will be selected and these row references will not change (even if you copied them) unless you reselect. Again select the Print Preview button to see the effect. Columns to repeat at left: does exactly the same thing, you just click in the box and then select the columns you wish to appear on each page.

You can adjust your printing further by going to Page Break Preview. This is found under the Workbook Views group on the View tab, or there are three Views buttons at the bottom right hand side of your screen next to the zoom bar. Click the one closest to the zoom bar. If you hover your mouse over them, you will see which is which. When you are in Page Break Preview you will be able to see both horizontal and vertical blue dashed lines. These denote where your pages end. Again, place your mouse on these lines to drag to the desired spot. You may only want to squash a column or two onto one page, or you can also use them to break your pages where you want to break them, rather than letting Excel decide where your pages should be broken.

EXCEL VBA: Working with arrays Part 2

Last month we saw how to fill arrays. This month we'll look at how to output arrays to ranges. Note the code uses "UBound(strArray) + 1" for a last row number in Column "A". This is because
an array always starts at Zero, unless we use; Option Base 1 at the very top of the Module. Also note that we also use the "WorksheetFunction.Transpose" so our array will fit the range. That is, by default, arrays are horizontal.

Sub OutputArrayVerticalRange()
Dim strArray()

            'Fill array
            strArray = Array("Zoo", "Cat", "Paddock", "Sheep", _
                        "Cow", "Bird", "Rat", "Chicken", "Fence", "Post", "Lamb")
                        
    'Output array to vertical range.
    Range("A1:A" & UBound(strArray) + 1) = WorksheetFunction.Transpose(strArray)
End Sub

Outputing to horizontal ranges means we can omit "WorksheetFunction.Transpose".

Sub OutputArrayHorizontalRange()
Dim strArray()

            'Fill array
            strArray = Array("Zoo", "Cat", "Paddock", "Sheep", _
                        "Cow", "Bird", "Rat", "Chicken", "Fence", "Post", "Lamb")
                        
    'Output array to horizontal range.
    Range(Cells(1, 1), Cells(1, UBound(strArray) + 1)) = strArray
End Sub

If we hard code our output ranges, and our last Column/Row is short, we only output part of our array.

Sub OutputPartArray()
Dim strArray()
Dim lRow As Long
Dim lElement As Long

            'Fill array
            strArray = Array("Zoo", "Cat", "Paddock", "Sheep", _
                       "Cow", "Bird", "Rat", "Chicken", "Fence", "Post", "Lamb")
   'Output part array
   Range("A1:A5") = WorksheetFunction.Transpose(strArray)
End Sub

We can also out array to a non-contiguous range with a stepped loop. Note that we still use +1 after we multiple the upper bound array by 2. If we used Step 3 we would multiple by 3

Sub OutputArrayNonContiguousRange()
Dim strArray()
Dim lRow As Long
Dim lElement As Long

            'Fill array
            strArray = Array("Zoo", "Cat", "Paddock", "Sheep", _
                        "Cow", "Bird", "Rat", "Chicken", "Fence", "Post", "Lamb")
                        
    'Output array to non-contiguous range
    For lRow = 1 To UBound(strArray) * 2 + 1 Step 2
            Cells(lRow, 1) = strArray(lElement)
            lElement = lElement + 1
    Next lRow
End Sub

Here are some useful links for working with arrays.

Creating Arrays: Understand how to create two types of arrays in Microsoft� Visual Basic� for Applications (VBA) � fixed-size arrays and dynamic arrays.
Arrays and Variants: Learn how a Variant variable can store an array.
Assigning One Array to Another: Assign one array to another if two dynamic arrays have the same data type.
Returning an Array from a Function: Call a procedure that returns an array and assign it to another array.
Passing an Array to a Procedure: Declare an array in one procedure, and then pass that array to another procedure to be modified.
Sorting Arrays: Understand how to sort an array, which is an iterative process that requires a complex algorithm.
Using the Filter Function to Search String Arrays: Search a string array if you simply must know whether an item exists in the array by using the Filter function.
Using a Binary Search Function to Search Numeric Arrays: Learn how the binary-search algorithm performs efficient searching on a sorted array � whether numeric or string.
Searching a Dictionary: Use object programming constructs, such as For Each�Next and With�End With statements, to work with the Dictionary object.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft