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