Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting print area with vba
Hi !
I would like to run vba code (to execute from a command button) that would determine the last entry in column A of the acitve worksheet and then set the print area of the active worksheet to that last row (entry) in column A..and stop at col J going across. The last column I would ever want to include in the print area is column J...The rows could go on and on...or stop at 20. For Example ... if the last entry in column A is cell A320, then the print area would be A1:J320 (col J would always be the stop point for going across). Thanks in advance for your help! Kimberly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting print area with vba
Hi KimberlyC,
This this: Private Sub CommandButton1_Click() ThisWorkbook.ActiveSheet.PageSetup.PrintArea = "A1:J" & LastInColumn(Range("A1")) ''get rid of this after testing MsgBox "Print area set to " & ThisWorkbook.ActiveSheet.PageSetup.PrintArea End Sub Function LastInColumn(rngInput As Range) ''Courtesy of http://www.j-walk.com, though a tad changed Dim WorkRange As Range Dim i As Integer, CellCount As Integer Application.Volatile Set WorkRange = rngInput.Columns(1).EntireColumn Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange) CellCount = WorkRange.Count For i = CellCount To 1 Step -1 If Not IsEmpty(WorkRange(i)) Then LastInColumn = WorkRange(i).Row Exit Function End If Next i End Function Ray at work "KimberlyC" wrote in message ... Hi ! I would like to run vba code (to execute from a command button) that would determine the last entry in column A of the acitve worksheet and then set the print area of the active worksheet to that last row (entry) in column A..and stop at col J going across. The last column I would ever want to include in the print area is column J...The rows could go on and on...or stop at 20. For Example ... if the last entry in column A is cell A320, then the print area would be A1:J320 (col J would always be the stop point for going across). Thanks in advance for your help! Kimberly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting print area with vba
This might be a simpler method. I always like to start at the bottom
of the worksheet and work up, in case of blank cells within the data section I want to define. -Glenn Sub SetPrintArea() Range("A65536").Select Selection.End(xlUp).Select intLastRow = ActiveCell.Row ActiveSheet.PageSetup.PrintArea = "$A$1:$J$" & intLastRow End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting print area with vba
Thank you Ray and Glenn!!
Both ways worked great!! "KimberlyC" wrote in message ... Hi ! I would like to run vba code (to execute from a command button) that would determine the last entry in column A of the acitve worksheet and then set the print area of the active worksheet to that last row (entry) in column A..and stop at col J going across. The last column I would ever want to include in the print area is column J...The rows could go on and on...or stop at 20. For Example ... if the last entry in column A is cell A320, then the print area would be A1:J320 (col J would always be the stop point for going across). Thanks in advance for your help! Kimberly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print Area Setting | Excel Discussion (Misc queries) | |||
Setting print area | Excel Discussion (Misc queries) | |||
setting print area via VBA | Excel Programming | |||
SETTING PRINT AREA IN VBA | Excel Programming | |||
Setting print area | Excel Programming |