print macro

[redf1re]

Hi, i have a dynamic table that may change its size depending on which information you want to see(the number of columns as well as the lines may change).
I need a macro to fit the information on the sheet of paper when the user chooses to print certain information, in landscape.
anyone can help me?

[Tom Ogilvy]

Turn on the macro recorder and setup your print options manually.

Look at Fit to pages wide by tall

Then look at the recorded macro. Remove any lines you do not need to set -
as every line you execute will take measureable time, so you want to execute
a minimum number of instrructions. You need to have the

.Zoom = False

line however.

--
Regards,
Tom Ogilvy

"redf1re" <redf1re.2492jy_1141651801.1779@excelforum-nospam.com> wrote in
message news:redf1re.2492jy_1141651801.1779@excelforum-nospam.com...
>
> Hi, i have a dynamic table that may change its size depending on which
> information you want to see(the number of columns as well as the lines
> may change).
> I need a macro to fit the information on the sheet of paper when the
> user chooses to print certain information, in landscape.
> anyone can help me?
>
>
> --
> redf1re
> ------------------------------------------------------------------------
> redf1re's Profile:
http://www.excelforum.com/member.php...o&userid=31258
> View this thread: http://www.excelforum.com/showthread...hreadid=519301
>

[Al]

To add to what Tom said, you also may want to set your table to a dynamic
range name. (a range name which expands or contracts based on the
data and the number of columns and rows)

Heres a link which explains how to create a dynamic range name.

http://www.ozgrid.com/Excel/DynamicRanges.htm

this range name should be referenced in the print macro.

"redf1re" wrote:

>
> Hi, i have a dynamic table that may change its size depending on which
> information you want to see(the number of columns as well as the lines
> may change).
> I need a macro to fit the information on the sheet of paper when the
> user chooses to print certain information, in landscape.
> anyone can help me?
>
>
> --
> redf1re
> ------------------------------------------------------------------------
> redf1re's Profile: http://www.excelforum.com/member.php...o&userid=31258
> View this thread: http://www.excelforum.com/showthread...hreadid=519301
>
>

[Udo]

Hi,

if I have a similar problem I use two approaches (described only for
rows, can be used for columns as well):
1) in a verly left column (which can be hidden) I write a formula which
checks the content of the relevant columns on the right hand side
(=if(...)). When the appropriate column should be visible a value is
given by the formula, if not "hidden" will be given. Then I have some
VBA code hiding the rows:
Dim rngArea As Range
Application.ScreenUpdating = False
ActiveSheet.Range("a16: a306").Select
Selection.SpecialCells(xlCellTypeFormulas, 2).Select
For Each rngArea In Selection
rngArea.EntireRow.Hidden = True
Next
2) When I have a block of data where I want to hide the lines with or
without any entry I use the following macro:
Sub HideEmptyRows()

Dim AktZ As Integer 'counter of row
Dim AktSp As Integer 'counts column
Dim StaSp As Integer 'Starting column
Dim StaZ As Integer 'Start row
Dim EndZ As Integer 'end row
Dim Inhalt As String 'content or no content
Dim i As Integer 'Standard counter
Dim Box As String
Dim Botschaft As String
Dim Abfrage As String
Dim ZU As String

'Information box
ZU = Chr(10)
Botschaft = "This routine is used to hide rows in a list,"
+ ZU + _
"where in the relevant is either nothing or something" +
ZU + ZU + _
"For this, the cursor has to be put into the upermost and
leftmost cell of the list." + ZU + ZU + _
"If this is correct, verify with 'Yes' otherwise cancel
with 'No'"

Box = MsgBox(Botschaft, vbYesNo, "Information")
If Box = vbNo Then
Exit Sub
End If
StaZ = Selection.Row
AktSp = Selection.Column
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown inserts supportive row
Cells(StaZ, 1).Select
For i = 1 To 25
Cells(StaZ, i).Select
Selection.Value = i 'includes numbers for
orientation
Next i
Cells(StaZ, AktSp + 1).Select

Botschaft = "Please enter the number of the relevant row"
+ ZU + _
"(see supportive numbering just obove the list)"
StaSp = InputBox(Botschaft, "Query for relevant column")

Botschaft = "If you want to hide rows which have a value
in the relevant column," + ZU + _
"please click on 'Yes', " + ZU + _
"if you want to hide those, where the relevant column is
empty, click on 'No'."

Inhalt = MsgBox(Botschaft, vbYesNo, "Query for content")
'result is no content = vbNo or content = vbYes

Application.ScreenUpdating = False

Selection.EntireRow.Delete 'removes row with
supportive numbers

Cells(StaZ, AktSp).Select
Selection.End(xlDown).Select
EndZ = Selection.Row
Cells(StaZ, StaSp).Select

AktZ = StaZ

Do Until AktZ > EndZ

Select Case Inhalt
Case vbNo
If Selection.Value = "" Then
Selection.EntireRow.Hidden = True
End If
Case vbYes
If Selection.Value <> "" Then
Selection.EntireRow.Hidden = True
End If
End Select

AktZ = AktZ + 1
Cells(AktZ, StaSp).Select

Loop
Application.ScreenUpdating = True

End Sub

Hope this helped.
Udo