Need help defining a print area in this macro.

[cbrf23]

Hi, I'm looking for some help editing this macro to make excel check for the last cell with a value in it and set the print area to end there. Problem is my blank cells have a formula in them which is hidden. The formula looks like

=IF(ISBLANK($A12),"",VLOOKUP($A12,FPI!$A:$E,2,FALSE))

. So,if the referenced cells are blank, then that cell appears blank...if the ref cel has data, then it displays the proper info. What I need help with, is making excel see these cells as blank. For example, can I get my macro to check a given range to see if the value of the cell="", and then set the print area to end at the first cell that the value =""? Could anyone explain this to me? I'm not super familiar with vb language...

Thanks!

The current macro looks like this:

Sub PRINTALL()
 
On Error Resume Next
Dim LOTQTY, SHT1, SHT2, SHT3, PPS1, PPS2, PPS3, RATIO
Dim MYSTART As Range
Set MYSTART = ActiveCell
 
RATIO = 0.8  'RATIO OF LOTQTY TO SHTQTY THAT TRIGGERS MULTIPLE SHEETS TO BE PRINTED
 
 
    Sheets("DATA ENTRY").Select
    Range("B13").Select
    PPS1 = ActiveCell.Value
    Range("B14").Select
    PPS2 = ActiveCell.Value
    Range("B15").Select
    PPS3 = ActiveCell.Value
 
    Range("A3").Select
    LOTQTY = Application.Max(ActiveCell.Value, 1)
    If Application.WorksheetFunction.MOD(LOTQTY, PPS1) / PPS1 < RATIO Then SHT1 = Application.WorksheetFunction.RoundUp(LOTQTY / PPS1, 0) Else SHT1 = Application.WorksheetFunction.RoundUp(LOTQTY / PPS1, 0) + 1
    Sheets("1").PrintOut Copies:=SHT1, Collate:=True
    If Application.WorksheetFunction.MOD(LOTQTY, PPS2) < RATIO Then SHT2 = Application.WorksheetFunction.RoundUp(LOTQTY / PPS2, 0) Else SHT2 = Application.WorksheetFunction.RoundUp(LOTQTY / PPS2, 0) + 1
    Sheets("2").PrintOut Copies:=SHT2, Collate:=True
     If Application.WorksheetFunction.MOD(LOTQTY, PPS3) < RATIO Then SHT3 = Application.WorksheetFunction.RoundUp(LOTQTY / PPS3, 0) Else SHT3 = Application.WorksheetFunction.RoundUp(LOTQTY / PPS3, 0) + 1
    Sheets("3").PrintOut Copies:=SHT3, Collate:=True
 
Sheets("FPI").PrintOut Copies:=1, Collate:=True
Sheets("CONFIGURATION").PrintOut Copies:=1, Collate:=True
 
Application.Goto Reference:=MYSTART
End Sub

 

[TiaXL]

start at a point and loop through activecell.offset(1,0).select until the value = "" like this

Sub test()
Dim rCell As Range

Range("b1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""

i just ran this over a column with your if(isblank) formula in and it stopped the loop on the second cell so appears to work and appears to treat formulas as non values.

you will then need to store the cell address to use when you set the print area.

 

[cbrf23]

TiaXL, thank you for the very quick response. Would you mind explaining how i might use that to define a print area in the macro I posted? I'm sorry, but I'm very unfamiliar with visual basic and I'm just not sure how to implement what you wrote.

 

[cbrf23]

Can anyone show me how to implement that code in the existing print macro I am working with?

 

[cbrf23]

Nobody to help me with this?