Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print has some blank rows but screen has data in rows | Excel Discussion (Misc queries) | |||
Print only rows with data entered in an excel template | Excel Discussion (Misc queries) | |||
Print data filled rows only | Excel Worksheet Functions | |||
do not print rows with zero data | Excel Discussion (Misc queries) | |||
Print single sheets from rows of data | Excel Programming |