Only print rows that contain data
 

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"

Only print rows that contain data
 

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!

"SITCFanTN" wrote:

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"

Only print rows that contain data
 

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

--
Regards,
Tom Ogilvy


"SITCFanTN" wrote:

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"

Only print rows that contain data
 

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"SITCFanTN" wrote:

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"

Only print rows that contain data
 

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


"SITCFanTN" wrote:

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"SITCFanTN" wrote:

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"

Only print rows that contain data
 

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.

--
Regards,
Tom Ogilvy


"SITCFanTN" wrote:

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"SITCFanTN" wrote:

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"