Limit Cells That Are To Be Printed Or Exported To PDF Document Or Hide Columns

**william516** · 07-04-2013, 05:05 AM

Ok here is the problem I have run into. I have a code on the page to limit the printing area to a certain amount of cells, but I'm finding that the code does nothing if it is exported to or converted to a .pdf document. There are several columns I am using for VBA coding information that do not need to be printed or displayed. Example of these columns would by J,k and L. J is being used for calculations and simply displays a text value. This column is then searched for a certain text phrase and it is counted and displayed on another sheet. K is simply a column that ranges from the #1 to 20000. This column was created to give a unique value to each row. This was per another user and so far everything has worked fine.

The problem is that if I "HIDE" the column the VBA code seems to crash because it needs to be able to see that code on the sheet to work. If I leave the codes on there and export or convert to a .PDF file it also takes those pages and now instead of a 50 page file it is 1000's of pages long. It keeps printing all the column "K" values. This is useless as in most inspections will not have that many numbers. I just made it so that any amount of devices could be added by the user or copied and pasted into the workbook.

I'm looking for a way to solve the problem of having these columns printed or converted to a .PDF file. If it is as simple as having the VBA code work from a hidden column that is fine. The idea is that I just want to print the information from columns A - I.

As it is the inspectors can not figure out how to get just the information they need.

Thanks for any help you can give.

I'm sorry for the explanation but I really don't know how to explain it any better.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195
https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909
https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**Excel Fox** · 07-04-2013, 04:14 PM

If you want to print it, you can manually set the print area from A to I

But if you want to create a PDF, you could use a macro that hides the unwanted columns, and then conver to PDF, and then unhide the columns back to the original state.

**william516** · 07-05-2013, 02:46 AM

**bakerman** · 07-06-2013, 05:29 AM

As I stated in my last reply to your other thread, set a Printarea and in the ExportAsFixedFormat-method set the IgnorePrintAreas-property to False.
For ex.

Code:

Sub ExportPdf()

    Const PdfPath = "D:\My documents\"
    Const PdfName = "Inspector"
    With Sheets("INITIATING DEVICES")
        .PageSetup.PrintArea = .Range("A1:I" & .Cells(Rows.Count, 4).End(xlUp).Row).Address
        .ExportAsFixedFormat xlTypePDF, PdfPath & PdfName & ".pdf", _
                0, False, False, , , True
    End With
    
End Sub

The PrintArea is now set to the Entire A-I range but you could make this dynamic by using Application.InputBox so you can define the PrintArea manually, or use AutoFilter to extract certain information and with SpecialCells export the Visible cells only.