Clean up Blank Pages

jnaranjo91

New Member
Joined
Jan 18, 2012
Messages
6
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,724
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

New Member
Joined
Jan 18, 2012
Messages
6
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,724
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

New Member
Joined
Jan 18, 2012
Messages
6
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,724
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
 
Last edited:

Forum statistics

Threads
1,082,276
Messages
5,364,195
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top