Tags: acitve, area, button, code, column, command, entry, excel, execute, microsoft, msdn, print, run, setting, software, vba, worksheet, woulddetermine

Setting print area with vba

On Microsoft » Microsoft Excel

3,649 words with 3 Comments; publish: Sun, 01 Jun 2008 12:01:00 GMT; (30678.13, « »)

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

All Comments

Leave a comment...

  • 3 Comments
    • 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" <kimberchia.excel.todaysummary.com.sbcglobal.net> wrote in message

      news:ey5xMOI5EHA.4004.excel.todaysummary.com.tk2msftngp13.phx.gbl...

      > 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

      >

      >

      #1; Sun, 01 Jun 2008 12:03:00 GMT
    • 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

      #2; Sun, 01 Jun 2008 12:04:00 GMT
    • Thank you Ray and Glenn!!

      Both ways worked great!!

      "KimberlyC" <kimberchia.excel.todaysummary.com.sbcglobal.net> wrote in message

      news:ey5xMOI5EHA.4004.excel.todaysummary.com.tk2msftngp13.phx.gbl...

      > 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; Sun, 01 Jun 2008 12:05:00 GMT