Setting a print area using VBA

[t16turbo]

Hello,

How can I go about setting the print area to be:

landscape
fit to 1 page tall by 1 page wide
and just selecting the datarange?

at the moment, it picks up a few thousand rows, even though there are often on ly 10 or so with data in - because the data has been filtered and deleted.
Is there a way I can tell the VBA to only select those rows WITH data to be the SET PRINT AREA range?

 

[SkipVought]

only select those rows WITH data to be the SET PRINT AREA range

I'd wager that Excel is doing just that!

Often cells that look as if there is no data in them DO HAVE DATA IN THEM, like SPACES, or other INFORMATION (formatting, for instance)

Here's how to visually find the symptoms.

View/Page Break Preview

This show on the sheet how Excel plans to print. Stuff that you don't want -- DELETE and watch the print area modify.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[lamyuwang]

hi t16turbo,

to set
" landscape
fit to 1 page tall by 1 page wide
and just selecting the datarange?"

you can click
file --> print area --> set print area
then select the area you want.
-->file--> page setup
then you can set up it print fit to 1 page, ....

I hope that is what you want~~

 

[KenWright]

You can reset the usedrange either manually or programmatically, with full instructions here:-

http://www.contextures.com/xlfaqApp.html#Unused

You can then use that 'cleaned' sheet area to determine your print range, eg amending Debra's code slightly such that it doesn't loop through all sheets, and then print previews your sheet at the end:-

Sub DeleteUnusedAndPrint()

    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim wks    As Worksheet
    Dim dummyRng As Range
    Dim rng    As Range

    Set wks = ActiveSheet
    With wks
        myLastRow = 0
        myLastCol = 0
        Set dummyRng = .UsedRange
        On Error Resume Next
        myLastRow = _
        .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByRows).Row
        myLastCol = _
        .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByColumns).Column
        On Error GoTo 0

        If myLastRow * myLastCol = 0 Then
            .Columns.Delete
        Else
            .Range(.Cells(myLastRow + 1, 1), _
                   .Cells(.Rows.Count, 1)).EntireRow.Delete
            .Range(.Cells(1, myLastCol + 1), _
                   .Cells(1, .Columns.Count)).EntireColumn.Delete
        End If

        Set rng = .Range(Cells(1, "A"), Cells(myLastRow, myLastCol))
        With .PageSetup
            .PrintArea = rng.Address
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        .PrintPreview
    End With
End Sub

Regards
Ken...........


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------