• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 60
  • Last Modified:

Print area

Hello
How to select print area based on cell for example if the print area A1:H17 but some time some if column not have any data so how to exclude that column from print
0
Jamal Albusaidi
Asked:
Jamal Albusaidi
  • 4
  • 3
  • 2
1 Solution
 
Roy CoxGroup Finance ManagerCommented:
It's hard to say without seeing an example. Are you using VBA or do you mean manually?

You could try Ctrl + G  to display the Go To wizard. Then Click Special and maybe Constants. This will pick up header rows though
0
 
Jamal AlbusaidiAuthor Commented:
Fine if it work by vba or any cell value
For example if cell value > 0 for that column will be include in print
0
 
Rob HensonFinance AnalystCommented:
For a small area, such as A to H you can set up Conditional Formatting for each column that highlights when the column is blank. Before printing just hide those columns and they won't show in the print.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Roy CoxGroup Finance ManagerCommented:
Try this VBA  approach, you might need to edit the range.
Option Explicit

Sub PrintPreviewSetUp()
    Dim rRng As Range
    Dim iX As Integer
    Set rRng = Sheet1.Range("A1").CurrentRegion

    For iX = 1 To rRng.Columns.Count
        Columns(iX).Hidden = Application.WorksheetFunction.CountA(rRng.Columns(iX)) = 1
    Next iX

    Sheet1.PrintPreview
    
     rRng.EntireColumn.Hidden = False
End Sub

Open in new window

printPreview.xlsm
0
 
Rob HensonFinance AnalystCommented:
The VBA approach could be added to the BeforePrint event of the ThisWorkbook module so that it triggers whenever the user tries to print.

Downside with any VBA routine is that it clears the Undo history. So in your scenario; the user makes some amendments, clicks Print and the VBA triggers to show the preview with the hidden columns as required; user notices on the Preview that they have done something wrong so cancel the Print but cannot then use the Undo button to rectify so have to reverse their actions manually.
0
 
Jamal AlbusaidiAuthor Commented:
It look work fine however is it possible to keep it based on one cell for each column so if for example A1= X than column A include in print but if A1 X not include in print and same for the rest of column
0
 
Roy CoxGroup Finance ManagerCommented:
Currently the code checks how many columns are empty using the counta  function. I can't see why you would want to manually do this. As  Rob says you could call this using BeforePrint
Option Explicit

Sub PrintPreviewSetUp()
    Dim rRng As Range
    Dim iX As Integer
    Set rRng = Sheet1.Range("A1").CurrentRegion

    For iX = 1 To rRng.Columns.Count
        Sheet1.Columns(iX).Hidden = UCase(Sheet1.Cells(1, iX)) = "X"
    Next iX

    Sheet1.PrintPreview
    
     rRng.EntireColumn.Hidden = False
End Sub

Open in new window

0
 
Jamal AlbusaidiAuthor Commented:
Great
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now