Clean up Blank Pages

[jnaranjo91]

I have a template which is 7 pages long, with a few page breaks, Each page has anywhere from 30-45 rows. Sometimes some of the pages in the middle do not need to be displayed so I hide them. I need to save the document as a pdf and then e-mail it as needed. I noticed that instead of being hidden in the pdf they are displayed as blank pages. Is there a way around this without VBA?

I was thinking I could have the data populate in another worksheet (Sheet 2). I'm thinking I can select each page and name it, Page1 (From Sheet1), Page2 (From Sheet 1), etc. A1:I43=Page1
On sheet2 cell A1=IF(Sheet1!B8>0,Page1,""), this gives me an error message or it will only display the item from the first cell not the entire table (A1:I43)

 

[Zack Barresse]

Hello, and welcome to the board!

Can you delete them? Not sure why they are there if they are going to be blank and then hidden. Or copy to a new workbook, delete rows, save as PDF. You can do that manually as well.

 

[jnaranjo91]

I could copy all of the data I need into a new Worksheet and just save that. I wanted to automate it all though. No I can't delete the cells because there's quite a few formulas in them that I need. The 7 pages are from a template, about 70% of the time I need all 7 pages and the rest could be any combination resulting in as little as 3 pages needed.

 

[Zack Barresse]

Oh, you said "without VBA", so I assumed you meant, "without VBA". If you do want VBA, then yes, it's quite possible. Do you know any VBA? Not sure what you know, don't know, have, don't have, version of Excel, etc. The VBA would go something like, create new workbook, copy data, paste as values, delete blank rows, save as PDF, do whatever else you wanted. Fill in the blanks, and we can help you with whatever you need.

 

[jnaranjo91]

Office 2010. I was avoiding the VBA, because I have never used it and don't even know where to start. To be honest I'm quite to new to using Excel in general. I've only been using it at work for about 6 months.
That sounds like exactly what I want though, if you could show me and say which values I would need to change.

 

[Zack Barresse]

Okay, this code makes some assumptions. Everything preceeded with a single apostrophe is a comment, please read them. It explains what the code directly underneath it is doing and will help you follow the logic, as well as help you spot anything you don't want to happen, so if we need to revise it you can point right to it.

This assumes that you want all blank rows deleted. I'm not sure if that's what you want or not, but we'll give it a go.

To install this code, which will reside in the workbook in question, follow these steps...

• From Excel, hit Alt + F11 (open the Visual Basic Editor)
• Hit Ctrl + R, to open the Project Explorer (may already be open, but that's ok)
• Find your workbook project, select it
• Click the Insert menu, select Module (should say "Module1")
• Copy/paste the code into the code pane on the right
• Close the VBE (or press Alt + Q to return to Excel)
• Press Alt + F8 to bring up the Macros window
• Select "PrepForPDF"
• Click Run

You can assign this macro to a button, make a custom Ribbon item/button to fire it off, whatever you want.

Also, look in the code for the "WORKSHEET NAME IN QUESTION GOES HERE" portion. This requires you to put the name of the worksheet here. If it's always the first worksheet (farthest left) you can use Worksheets(1) instead of a text string.


<font face=Tahoma><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> PrepForPDF()<br><br>    <SPAN style="color:#007F00">'Dimension variables</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wbOld                   <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">Dim</SPAN> wbNew                   <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">Dim</SPAN> wsOld                   <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wsNew                   <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> iRow                    <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> iLastRow                <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> iLastCol                <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> vFileName               <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sName                   <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    Dim sPath                   <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#007F00">'Set old workbook/worksheet objects</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wbOld = ThisWorkbook<br>    <SPAN style="color:#00007F">Set</SPAN> wsOld = wbOld.Worksheets(1)<br><br>    <SPAN style="color:#007F00">'Ask for save path and name</SPAN><br>    vFileName = Application.GetSaveAsFilename(wbOld.Name)<br><br>    <SPAN style="color:#007F00">'Check if valid file name/path were chosen by user</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> vFileName = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>    <SPAN style="color:#007F00">'user pressed cancel</SPAN><br>    sName = Right(vFileName, Len(vFileName) - InStrRev(vFileName, "\"))<br>    sPath = Left(vFileName, Len(vFileName) - Len(sName))<br><br>    <SPAN style="color:#007F00">'Check name and path for continuity</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Right(sPath, 1) <> "\" <SPAN style="color:#00007F">Then</SPAN> sPath = sPath & "\"<br>    <SPAN style="color:#00007F">If</SPAN> LCase(Right(sName, 4)) <> ".pdf" <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Right(sName, 1) = "." <SPAN style="color:#00007F">Then</SPAN><br>            sName = sName & "pdf"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            sName = sName & ".pdf"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#007F00">'Check if file exists</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Dir(sPath & sName, vbNormal) <> "" <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "A file already exists with that name in that location.  Please try again.", vbCritical, "ERROR!"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#007F00">'Set new workbook/worksheet objects</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wbNew = Workbooks.Add(xlWBATWorksheet)<br>    <SPAN style="color:#00007F">Set</SPAN> wsNew = wbNew.Worksheets(1)<br><br>    <SPAN style="color:#007F00">'Find last row & column of data in current worksheet (to be copied)</SPAN><br>    iLastRow = wsOld.Cells.Find(What:="*", After:=wsOld.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row<br>    iLastCol = wsOld.Cells.Find(What:="*", After:=wsOld.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column<br><br>    <SPAN style="color:#007F00">'Copy old data to new worksheet</SPAN><br>    wsOld.Range("A1", wsOld.Cells(iLastRow, iLastCol)).Copy wsNew.Range("A1")<br><br>    <SPAN style="color:#007F00">'Loop from the bottom, delete any all blank rows</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> iRow = iLastRow <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">If</SPAN> WorksheetFunction.CountA(wsNew.Range(wsNew.Cells(iRow, 1), wsNew.Cells(iRow, iLastCol))) = 0 <SPAN style="color:#00007F">Then</SPAN><br>            wsNew.Rows(iRow).Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> iRow<br><br>    <SPAN style="color:#007F00">'Save new workbook as PDF</SPAN><br>    wsNew.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName, Quality:=xlQualityStandard, OpenAfterPublish:=<SPAN style="color:#00007F">False</SPAN><br><br>    <SPAN style="color:#007F00">'Close new workbook without saving (discard changes)</SPAN><br>    wbNew.Close SaveChanges:=<SPAN style="color:#00007F">False</SPAN><br><br>    <SPAN style="color:#007F00">'Give message user has completed process</SPAN><br>    MsgBox "Process complete!" & vbNewLine & vbNewLine & "File saved to:" & vbNewLine & sPath & sName, vbExclamation, "SUCCESS!"<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Post back if it doesn't work or you need something else. Be sure to save a copy of your work first, just as a good safeguard. I have tested the code and it works.

EDIT: Added a check to see if the file already existed and added a complete message box.

HTH