Set Print Range to First 3 Consecutive Blank Cells

admsteff

New Member
Joined
Jun 16, 2017
Messages
2
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.).

Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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: http://www.mrexcel.com/forum/board-a...forum-use.html. 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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top