Print range based on cell value

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
91
I’m looking for a way (I assume VBA code) to only print rows where column B is displaying data (i.e., B<>””). All cells in column B have a formula in them but depending on data input, many will appear blank; therefore, the number of applicable rows varies. That is, sometimes I may want to print B30:K35, other times it may be as large as B30:K777. The entire potential print range is B30:K1000.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try with this


Code:
Sub Macro11()
'
    ActiveSheet.PageSetup.PrintArea = ""
    uc = Range("B30").SpecialCells(xlLastCell).Column
    i = 30
    Do While Cells(i, "B") <> ""
        i = i + 1
    Loop
    rng = Range("A30", Cells(i - 1, uc)).Address
    ActiveSheet.PageSetup.PrintArea = rng
    ActiveSheet.PrintOut
End Sub
 

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
91
Thanks for your response Dante. I'm receiving a Compile Error "Variable not defined for (UC =)". Do I need to define the entire potential print range (i.e., B30:K1000)?
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Just need to declare variables

Try this:

Code:
Sub Macro11()
'
    Dim i As Double, rng As String
    ActiveSheet.PageSetup.PrintArea = ""
    i = 30
    Do While Cells(i, "B") <> ""
        i = i + 1
    Loop
    rng = Range("B30:K" & i - 1).Address
    ActiveSheet.PageSetup.PrintArea = rng
    ActiveSheet.PrintOut
End Sub
 

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
91

ADVERTISEMENT

Just need to declare variables

Try this:

Code:
Sub Macro11()
'
    Dim i As Double, rng As String
    ActiveSheet.PageSetup.PrintArea = ""
    i = 30
    Do While Cells(i, "B") <> ""
        i = i + 1
    Loop
    rng = Range("B30:K" & i - 1).Address
    ActiveSheet.PageSetup.PrintArea = rng
    ActiveSheet.PrintOut
End Sub

Dante,

It prints however it prints nothing. Here is a screenshot of my worksheet. In this case, I only want 10 lines to print. The last line should be Row 42.

I'm trying to include a screenshot but this is not letting me.

Sorry,
SKK
 

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
91
Got it. I noted row 30 as start of print range. It should have been 33. Rows 30-32 are a header. I changed the code to read 33 and all appears to be fine.

Thank you very much Dante - much appreciated,
Steve
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. I appreciate your kind comments.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,008
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top