Set print area based on a cell value with vba

  • I am inexperienced with vba and have been using an if formula code to set print area on some tags on a worksheet. It has worked fine except I now need to drastically expand the number of tags. Is there another option I could use? Offset? Example follows and continues through the tags.

    If  Range (A23) . Value>0 Then
    If  Range (A48) . Value>0 Then

    Thank you for any time you can give me.
    Mike Jaco

  • Re: Set print area based on a cell value with vba

    Is there a defined area that will contain the flag?

    What could be done is monitor that area of the worksheet for changes. When a change is made, all other cells are set to 0 and then the print area set accordingly...

    I notice you started and ended your code with a square bracket - think you were trying to add code tags.

    The proper syntax is

    [/noparse][/b]   '// Your code here[b][noparse]


    Your thread has been edited to correct.

  • Re: Set print area based on a cell value with vba

    Hi, mikejaco,

    some parts of your code must have gone while copying.

    I can´t figure out a reason to start at the top for the loop (I´d preferred to start from the bottom up as the last will overwrite any existing pagesetup). Code may be applied to the Workbook_BeforePrint(Cancel As Boolean) event in ThisWorkbook and may be altered to reflect a certain sheet or find out the last used row on that sheet:

    Dim lngCounter As Long
    For lngCounter = 0 To 10
      If Cells(23 + lngCounter * 25, "A").Value > 0 Then
          ActiveSheet.PageSetup.PrintArea = Range(Cells(1 + lngCounter * 25, "A"), Cells((lngCounter + 1) * 25, "K")).Address
      End If
    Next lngCounter

    Dim lngCounter As Long
    For lngCounter = 23 To 223 Step 25
      If Cells(lngCounter, "A").Value > 0 Then
          ActiveSheet.PageSetup.PrintArea = Range(Cells(lngCounter - 22, "A"), Cells(lngCounter + 2, "K")).Address
      End If
    Next lngCounter


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!