Using a Macro Button to set a print area based on last empty cell

[JeremyA1976]

Hi all,
I am trying to set up a button on my main form that I need to do the following steps. I have tried the record macro, but I can't get it to work.

Step 1:
I need the code to search for the last row with no content in column E.

Step 2:
Select an array of cells from the determined last empty row at column AA up to cell A1

Step 3:
Create the print area based on the array selected in step 2.

Can someone help point me in the right direction? I am interested in learning HOW it was written and why, not just the solution, so any explanation above and beyond is appreciated!
Thanks,
Jeremy

 

[Veritan]

Hi Jeremy, glad to see you're interested in the how and why. First, this is the code you're looking for.

Sub SetPrintArea()
    ActiveSheet.PageSetup.PrintArea = _
        "A1:AA" & Cells(Rows.Count, "E").End(xlUp).Offset(1).Row
End Sub

What it does is first reference the active sheet and its page setup property. A print area requires an actual string, so giving it a range reference like "Range("A1:AA" & Cells(Rows.Count, "E").End(xlUp).Offset(1).Row)" won't actually work. If you want, you can put the $ signs in to make the reference absolute, though it shouldn't need them. The & symbol is used to concatenate (a.k.a. join) strings of text together. The Cells command tells Excel to count the number of rows available on the sheet. While you don't strictly speaking need this, older versions of Excel had ~65,000 rows while newer versions have over 1 million rows. The Rows.Count method will always pick the correct last row, regardless of which version you are using. Finally, the End(xlUp) is the same as hitting Ctrl + Up Arrow. It will find the first non-empty cell going upwards from the last cell in column E. The Offset command then tells it to go 1 row downwards from its current position (you mentioned wanting the last empty row, so this includes a blank row; you could very easily take the Offset command out). The Row at the end gets the row number of that particular cell and joins it onto the "AA", giving you a fairly traditional Excel range reference. You can check it by going to the Name Manager in the Formulas tab and selecting the Print_Area named range. You can then assign this macro to any button on your worksheet and you should be good to go!

P.S.: The underscore at the end of the first line of code is used to join 2 or more lines of code into 1. It's completely unnecessary for the code to run, you could just put both lines on 1 and it will work fine, but this can make it easier for a human to read it, especially if you have a smaller screen

 

[JeremyA1976]

For whatever reason, I had to add those dollar signs to make it absolute. I was getting an error after running the macro once. so, i am assuming it considers where you have the cell active as A1? Once I added the dollar sign, it works fantastically! I actually needed 7 extra rows, but I knew i could manipulate that part of the code to what i needed. I learned a TON by what you offered in explanation and I am more than grateful! I am starting to understand the code, but there are so many variations of options to reach point B that it can be confusing at times.

On a different note, if you care to explain, is there a way to bring up a form that forces the user to enter specific information like Project, Title, Job Number, Description, etc.? and automatically fill in certain cells in the bid form at initial execution of the application template? I would want to create a filename, in a specified directory and "save as" to leave the template, so it can never be altered unless by me. Then any information that is entered on the form would be simply saved at completion of take-off of material. My main goal is to interlock the set print area button (Your code) as the final action. It would save, set print area, print to .pdf, close application.

I would have to imagine this task would be daunting. But I like punishment, so if you or anyone would have any guidance on any of these steps, I would appreciate it. Knowledge is king. Thanks a ton Veritan, for your great explanation.

 

[Veritan]

Okay, that's quite a lot of information you're looking for What you're asking is definitely something that you can accomplish. However, it would take a lot more explanation than I can give in a forum post! One of my favorite places to go to learn about VBA has been Wise Owl, and they have a series of videos on YouTube that is an excellent tutorial for this. It sounds like you're specifically interested in User Forms, Event code, and File System Objects. My recommendation would be to look through those videos and see if they can help out. Best of luck with this, it sounds like you're on the right track!

 

[JeremyA1976]

Perfect! Thanks a ton Veritan. I actually have most of that figured out with the exception of a message box upon opening the template. I will check out your recommendations, but I really just needed the search words to help investigate, which you thankfully supplied! not sure how to give a thumbs up on here, but consider this one!