Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using Excel 2007. I have an estimate sheet, which I will use to keep track of client information. There is some low level information that the client shouldnt see on their version of the document. I am looking to achieve the following results:

Only rows with data in the first cell "Task number" in Column A will be shown when printing. The way i believe this is doable is VBA script to hide all rows without data in the first cell when printing and unhide them afterwards.

Link to document template on Google Docs

I have included a link to a template document. If possible the middle section should be expandable to take on extra tasks etc, although probably easiest just to have 100 cells in the middle section which abide by the hiding rules.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

Here is a stab at the code you require. Hiding and unhiding rows is easy if you know how. I printed a worksheet with the macro recorder on so you can see how you could tailor the printing if you wish. I would set a shortcut key to run this macro.

Option Explicit
Sub PrintNonBlankColA()

  Dim RowCrnt As Integer
  Dim RowLast As Integer

  ' Note: This operates on the active worksheet

  Application.ScreenUpdating = False

  RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row

  ' Hide all rows with a used cell and column "A" empty
  For RowCrnt = 1 To RowLast
    If IsEmpty(Cells(RowCrnt, "A")) Then
      Range(RowCrnt & ":" & RowCrnt).EntireRow.Hidden = True
    End If
  Next

  ' For the following statements, I switched on the macro recorder,
  ' printed a sheet with all the headers and footers I wanted,
  ' switched off the macro recorder and copied the code out of the
  ' saved macro.
  ' Consider: .CenterHeader = "Activities for Acme Inc"
  ' If you name the worksheets for the client, the following would
  ' give you a heading for the appropriate client:
  ' .CenterHeader = "Activities for " & ActiveSheet.Name
  With ActiveSheet.PageSetup
      .PrintTitleRows = ""
      .PrintTitleColumns = ""
  End With
  ActiveSheet.PageSetup.PrintArea = ""
  With ActiveSheet.PageSetup
      .LeftHeader = ""
      .CenterHeader = "Activities for Acme Inc"
      .RightHeader = ""
      .LeftFooter = "&D"
      .CenterFooter = "Page &P of &N"
      .RightFooter = "Copyright Nadir Co."
      .LeftMargin = Application.InchesToPoints(0.75)
      .RightMargin = Application.InchesToPoints(0.75)
      .TopMargin = Application.InchesToPoints(1)
      .BottomMargin = Application.InchesToPoints(1)
      .HeaderMargin = Application.InchesToPoints(0.5)
      .FooterMargin = Application.InchesToPoints(0.5)
      .PrintHeadings = False
      .PrintGridlines = False
      .PrintComments = xlPrintNoComments
      .CenterHorizontally = False
      .CenterVertically = False
      .Orientation = xlPortrait
      .Draft = False
      .PaperSize = xlPaperA4
      .FirstPageNumber = xlAutomatic
      .Order = xlDownThenOver
      .BlackAndWhite = False
      .Zoom = 100
      .PrintErrors = xlPrintErrorsDisplayed
  End With
  ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

  ' Unhide all rows
  Cells.EntireRow.Hidden = False

End Sub

Loading above code to a module

From within Excel:

  • Select Tools then Macro then Visual Basic Editor. Alternatively, click Alt+F11. You will probably see the Project Explorer down the left, the Immediate window in the bottom with the bulk of the screen grey.
  • Select Insert then Module. The grey section will go white.
  • Copy and paste my code into the now white section. The macro can now be run against any sheet.

Using macro

  • Switch to Excel.
  • Select Tools then Macro then Macros.... Alternatively, click Alt+F8. A small Macro window will be displayed. You will only have one macro so it will be selected and the Run button will be active.

You can run the macro by clicking Alt+F8 then Enter but you might find the following more convenient.

  • From the Macro window, select Options. You will now see a small Macro Options window.
  • Enter a letter (I always use q) into the little box and click OK.
  • Close the Macro Window.

You can now switch to the appropriate client's work sheet and click Ctrl+q to run the macro.

Adjust the macro to your requirements

In the middle of my code is my idea of how you might set up a report with "Activities for Acme Inc" at the top and date, page number and copyright at the bottom.

Replace this code as follows:

  • From Excel, select Tools then Macro then Record New macro. You see a small Record Macro window.
  • Make sure the text box under "Store macro in:" says "This Workbook".
  • Click OK. The window disappears. You may see a very small window with a square button labelled Stop recording. If this window is visible, ignore it for the moment.
  • Print a client report with whatever headings, footers, margins, etc. you require.
  • If you can see the small window, click it. If you cannot see it, select Tools then Macro then Stop Recording.
  • Switch to the Visual Basic Editor. There will be a new Module. Click on it in the Project Explorer Window.
  • You will see Sub Macro1() all the statements to print a client report the way you like and End Sub.
  • Copy those statements (not sub and end sub) and paste them over the top of the equivalent statements in my macro.

If you do not include anything like client name in your report headers and footers, your macro will be ready for use. I give one example in my code where I use the worksheet name the report header. Without knowing your exact requirements I cannot be more precise but I hope this gives you a start.

share|improve this answer
    
Hi sorry im not that great with excel, I right clicked tab view code and entered this in its entirety into worksheet to test, seems to do what I need however I have no idea what the macro explorer is and how I would set that up. –  Gerard Jan 5 '12 at 13:38
    
When on your active sheet, press ALT + F8 to open the macro window. Select PrintNonBlankColA and then click the RUN button. –  CharlieRB Jan 5 '12 at 14:17
1  
Have you deleted the copy of the macro that you recorded against a sheet? If you kept that copy and then copy that sheet, you end up with two copies of the macro. If you set Ctrl+q to run the first copy, you will now have two macros to be run with Ctrl+q and it will not run either. If you click Alt+F8 you will see a list of macros. Do you see: Sheet2.PrintNonBlankColA, Sheet4.PrintNonBlankColA, PrintNonBlankColA or something similar. If so, Excel does not know what to do. –  Tony Dallimore Jan 5 '12 at 17:04
1  
If you have made changes, you need to decide which version is the master version. Open a blank NotePad document and copy the master version to it. Delete all macros from all sheets and all modules. Copy the master version from NotePad to Module 1. Redefine the "Print" key. I hope that fixes the problem. –  Tony Dallimore Jan 5 '12 at 17:07
1  
When I wrote the macro, I tested it from the editor. When I tried duplicating the macro, I ran it from Excel where I noticed it flickers during printing. I omitted Application.ScreenUpdating = False which I have now added at the top of the macro to fix this problem. –  Tony Dallimore Jan 5 '12 at 17:19
show 2 more comments

So whats the question? If its "How do I hide cells/rows when printing" then the answer is to use the Workbook_BeforePrint event to set up the sheet for printing, execute the print, then restore the prior view.

share|improve this answer
    
What formula / VBA script would check to see if the task has a number if it does not then that row is hidden when i print the document automatically? –  Gerard Jan 5 '12 at 13:27
add comment

You can try selecting the cells you want hidden, go to format, then protection, then hidden. Then select the review tab, protect sheet. Keep the password blank, then print.

When you want the cells back, just press unprotect sheet.

A bit more tedious then doing it with a macro, but simpler at the same time.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.