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
Jamal AlbusaidiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jamal AlbusaidiAuthor Commented:
Great
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.