Only print rows that contain data

[Guest]

I have this code to print rows 1 - 35 however I would just like to print the
rows that have data in col B because often I'm printing an empty page by
printing all 35 rows. I would like the print area to print col A - F but
only when there is text in Col B of that row, how would I code that. I
appreciate your help and so do the trees

ActiveSheet.PageSetup.PrintArea = "$A$1:$F$35"

 

[Guest]

An easy answer to this would be to enter =COUNTA(B1:B35) in cell B36 then
create some code to test the value of that cell and if greater than 0, to
print the page. I guess that even if there are only one or two rows with a
value in B, you still need to print one sheet but at least it'll stop sending
a blank sheet the the printer!

 

[Guest]

ActiveSheet.PageSetup.PrintArea = _
"$A$1:$F$" & cells(rows.count,"B").End(xlup).row

 

[Guest]

Does this code have to be attached to a cmd button Tom? I'm wondering
because I added it to my worksheet code and it is still printing two pages
even if I only have text in 5 rows in cols B.

FYI the reason I'm looking at text in Col B is because I have the sheet
prefilling with row numbers in col A.

Thanks again for your help.

 

[Guest]

This might be more than you want. It creates a new sheet, copies only the
rows with data in column B to the new sheet and prints it. If there is no
data, it does not print. You will need to change Worksheets(2) to your sheet
designation.

Sub prtColB()
Dim i As Integer
i = ActiveWorkbook.Sheets.Count
Sheets.Add Type:=xlWorksheet, after:=Sheets(i)
Set NewSheet = Worksheets(i + 1)
Worksheets(2).Activate
For Each c In Range("B1:B35")
If c <> "" Then
Range(c.Address).EntireRow.Copy
NewSheet.Activate
If Cells(1, 1) = "" Then
ActiveSheet.Paste
Else
Cells(1, 1).Activate
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
Worksheets(2).Activate
End If
Next
Application.CutCopyMode = False
NewSheet.Activate
If Not IsEmpty(Cells(1, 2)) Then
ActiveSheet.PrintOut
End If
NewSheet.Delete
End Sub

 

[Guest]

It has to execute before you print.

Where you put it would be your call. You might want to consider the
Beforeprint event.

It assumes the unfilled cells in column B are actually empty and not
prefilled with some conditional formula that makes the cell look empty, but
isn't.