Setting the PrintArea of a Resized Range RRS feed

  • Question

  • I'm trying to print a page with data in some of the cells (a list of names). I'm using this code:

             Dim rangeT As Range
             Set rangeT = Range("a14", ActiveSheet.Range("a65536").End(xlUp))
             rangeT.Resize(, 33).Select
             ActiveSheet.PageSetup.PrintArea = rangeT.Address

    I want to print the entire page, not just those rows with data, so that's why the End(xlUp).  The problem when I run this is that the sheet shows the correct range highlighted, but the Printpreview shows only the 1st column....

    What am I doing wrong???

    Thanks so much!


    Wednesday, May 14, 2014 6:54 PM

All replies

  • You're only selecting column A so only column A is being printed. Not sure why the resize statement. It's safer to rename your worksheets otherwise someone inserting a new one will break your code. Here I assume worksheet 2 has been renamed Data:

        Dim Rng As Range
        Set Rng = Range("a14", Cells.SpecialCells(xlCellTypeLastCell))
        ActiveSheet.PageSetup.PrintArea = Rng.Address

    Rod Gill
    Author of the one and only Project VBA Book

    Wednesday, May 14, 2014 7:45 PM
  • Thanks so much, Rod! 

    I've got data in column A, but no data in column AG (last column I want to print). So I find all pages with data on it on Col A using the End(xlUp) statement, then resize it to use the same number of rows, but to include all columns to AG (col 33). So the Cells.SpecialCells code you gave me doesn't work....

    Wednesday, May 14, 2014 7:58 PM