Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
print macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
print macro
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
print macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
print macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
Macro to open print window and set to print entire workbook | Excel Discussion (Misc queries) | |||
Create a print macro that would automatically select print area? | Excel Worksheet Functions | |||
Why does macro speed slow after Excel Print or Print Preview? | Excel Programming | |||
Need Help w/ Print Macro to Print All Visible Sheets (including Charts) in a Workbook | Excel Programming |