Print worksheet

Hi,
I have a Command Button to print a worksheet. On that worksheet, there are potentially several hundred rows of data in A:M. The rows are only filled under certain circumstances, but the formula to populate them has been copied down the sheet to infinity. When I try to print the sheet, it wants to print cells that are blank but have a formula contained in them. Does anyone know how I can only print those cells that contain text and not the ones that are blank and only contain formulas?

Thank you!

you can specify the range to print using

ActiveSheet.PageSetup.PrintArea

search this forum or google for examples.
Good luck

Thanks for the help. The print area will be undefined though. Each month may have a different number of occupied cells, and the end-users for this will not know how to search or filter for this.

Mate, try to set the print area conditionally based on the last occupied cell. Something like this:

Sub Print_Sheet()
Dim l As Long
With ThisWorkbook.Worksheets("Sheet1")                'replace Sheet1 with actual worksheet name
     For l = 1048576 To 1 Step -1                     'moving from last row up
          If .Range("A" & l) <> "" Then               'find the cell that has something filled
               .PageSetup.PrintArea = "$A$1:$M$" & l  'treat that as the last row for print range
               .....................
               <Code for printing a specified Print Area>
               .....................  
               Exit For
               End If
          Next l
     End With
End Sub

HTH!

Thanks. I added the command, and it froze everything up for a few seconds and nothing printed. Here's the workbook as it is now.

Mate, I was hoping that you will replace the following part of the code with the printout code:

.....................
<Code for printing a specified Print Area>
.....................

Nevermind, try this instead:

Private Sub cmdPrint_Click()
ThisWorkbook.Sheets("Sheet4").Cells(1, 4) = ComboBox1.Text

Dim l As Long
With ThisWorkbook.Worksheets("Sheet3")                'replace Sheet3 with actual worksheet name
     For l = 10000 To 1 Step -1                       'moving from last row up
          If .Range("A" & l) <> "" Then               'find the cell that has something filled
               .PageSetup.PrintArea = "$A$1:$M$" & l  'treat that as the last row for print range
               .PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
               Exit For
               End If
          Next l
     End With
End Sub

Also see attached - Teletype Form 3.xlsm
HTH!

Sorry man. Thanks. I will give it a go when I return to work tomorrow. Thank you!

Worked like a charm!!!! For your code, if the user inadvertently hits the button to print and the ComboBox is still blank, how could I prevent unnecessary copies? Since this is triggering an auto-print, I also don't want the user to print the last report...only the one currently being requested.

To prevent inadvertent printing see attached - Teletype Form 3.xlsm
I'm not really sure by what is implied by

I also don't want the user to print the last report...only the one currently being requested.

Please exemplify.

If the user accidentally hits the Print button rather than the submit, it will reprint the last month requested since that information still appears on the sheet.

Can it be altered to a Print Preview so that only certain pages can be printed? The document could be extremely long depending upon how much was done during the month.

Private Sub CommandButton2_Click()
ActiveSheet.PrintPreview
End Sub

or

Private Sub CommandButton2_Click()
If ActiveSheet.Name <> "Sheet2" Then _
  Sheets("Sheet2").Range("A1:E12").PrintPreview
End Sub

or

Range("A1:A6").PrintPreview

Private Sub cmdPrint_Click()

ThisWorkbook.Sheets("Sheet4").Cells(1, 4) = ComboBox1.Text

If ComboBox1.Value = "" Then
MsgBox "You Must Select A Month First"
Loop Until Combox1.Text <> ""


End If


Dim l As Long
With ThisWorkbook.Worksheets("Sheet3")                'replace Sheet3 with actual worksheet name
     For l = 10000 To 1 Step -1                       'moving from last row up
          If .Range("A" & l) <> "" Then               'find the cell that has something filled
               .PageSetup.PrintArea = "$A$1:$M$" & l  'treat that as the last row for print range
               .PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
               Exit For
               End If
          Next l
     End With


End Sub

My apologies, I posted an earlier version of the code. At the top you will see my failed attempt to get it to work. Apparently, I'm missing Do