Hide cells when printing in Excel

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 shouldn't 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

If possible, the middle section should be expandable to take on extra tasks and such, although it's probably easiest just to have 100 cells in the middle section which abide by the hiding rules.

Answer

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.