Results 1 to 3 of 3

Set Print Range to First 3 Consecutive Blank Cells

This is a discussion on Set Print Range to First 3 Consecutive Blank Cells within the Excel Questions forums, part of the Question Forums category; Hello, I've been browsing this forum quite a bit lately, as I've just started getting into vba coding a bit ...

  1. #1
    New Member
    Join Date
    Jun 2017

    Default Set Print Range to First 3 Consecutive Blank Cells

    Hello, I've been browsing this forum quite a bit lately, as I've just started getting into vba coding a bit in the last few weeks. It's been invaluable.

    Here's my question: I have a workbook that I set up to automatically set the print range upon saving (spent half a day trying to make it work with the BeforePrint event before learning that it's outdated) by looking for blank cells within column A, and ending the range at that blank row. But after using this for a few days I realized that while it works for Sheet1, for Sheet2, I need to actually find the first 3 consecutive blank cells in a column, and only then end the print range.

    Can I make this work? The code I have was found here and modified by half intuition and half trial-and-error, and is posted below (I left this sub wholly intact, even though there's a couple lines devoted to adding some Last Saved information in a couple cells.).

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheets("Sheet1").Range("C1").Value = Date & " " & Time
    Sheets("Sheet1").Range("C2").Value = Author
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = [LOOKUP(2,1/(A2:A250<>""),ROW(A1:A250))]
    ws.PageSetup.PrintArea = ws.Range("A1:C" & lastRow).Address
    Set ws = ThisWorkbook.Sheets("Sheet2")
    lastRow = [LOOKUP(1,1/(A1:A250<>""),ROW(A1:A250))]
    ws.PageSetup.PrintArea = ws.Range("A1:C" & lastRow).Address
    End Sub

  2. #2
    MrExcel MVP
    Joe4's Avatar
    Join Date
    Aug 2002

    Default Re: Set Print Range to First 3 Consecutive Blank Cells

    Welcome to the Board!

    It might be helpful if you could post an image of your data, and then tell us how you print range should be set, based on that image.

    You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jun 2017

    Default Re: Set Print Range to First 3 Consecutive Blank Cells

    I'm having lots of trouble getting the image posting methods to work correctly, see below.

    For now, if it helps for me to clarify the issue: I'm trying to print a sheet of "tags" or "labels."

    To do this, I have a label sheet (Sheet2), which pulls info from a PivotTable on Sheet3, and works by arranging 3 position-referenced values into 3 stacked cells per formatted label (say, for example, it shows the values from within my PTable A1,A2,A3, stacked into one label), then moves right to Column B and does the next 3 (it actually skips one PTable line, so now A5,A6,A7 stacked), then finally the same pattern for column C (say A9,A10,A11), before moving down (to Row 4) to repeat the process for as long as the table data goes. The in-cell formulas on my label sheet ignore blank data from the table, so the rest of the sheet remains empty, with no values shown. All of the above is working correctly.

    My print range for Sheet2 should be 3 columns wide, which is already being set correctly via my previously pasted vba code. The print range also auto-sets to find the first blank line (technically it looks for blank column A cells), and it does this correctly as coded, setting the print range at the row above the first blank A* cell on the label sheet. But it turns out that there will occasionally be blank data in ONE table category, always the second slot (in my above example, it would be the second cells: A2, A6, and A10). These blanks stop the print range, as they should, since my VBA code is working. But I need the range to continue until it finds either: A blank "top" value (in above example this would be A1, A5, A9, etc.) since this "name" category will never be blank, OR 3 consecutive blank lines (I suppose even 2 consecutive blank lines should work, but all 3 is a truly empty label, and I'd rather not find out later that I hadn't considered some odd circumstance). I assumed finding 3 consecutive blanks would be easier than jumping cells, but perhaps not, maybe it could look for blanks only in every third row. I don't think either way should be a problem, as long as the final row of the print range is set correctly.

    Also, I apologize for not being able to work the image posting methods. After putting some good time on it, I'm still having trouble using the HTML maker add-in: even when the add-in is checked and "active," I cannot find the service anywhere the via right-click or any other menus. The borders copy-paste does not seem to be working for me either, despite checking all the relevant settings. I'm using Excel 2016.

    If the above is not enough info, then maybe I can do some deeper searching on here and mess with the HTML add-in some more or try yet another method.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts