Print range based on cell value

[EssKayKay]

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.

 

[DanteAmor]

Try with this

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]

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)?

 

[DanteAmor]

Just need to declare variables

Try this:

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]

Just need to declare variables

Try this:

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]

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]

I'm glad to help you. I appreciate your kind comments.