Set a print area to capture all data on a sheet

[Harribone]

Hi,

I have a problem which can be easily solved with VBA but as I have not yet attempted to learn VBA I am turning to the fountain of knowledge that is the Excel Forum.

Basically I have a template spreadsheet and once I populate this with data I need to set a print area before printing. To automate this process I am looking to use some VBA.

In column H a cell will show 'END OF FILE' below the last row of data. For the purpose of explaining lets say this happens to be in cell H101.
I need VBA code to search column H to find this cell (I tried Cells.Find("END OF FILE") but that looks at the formulas not the actual value as I have found out). Once I know this I can then set the print range to ActiveSheet.PageSetup.PrintArea = "$F$1:$DH$100" but the '100' is a variable dependent on the location of END OF FILE (it will be the row number minus 1).

Hopefully that made sense, if not I will sort out some mock data to help explain - let me know if this is required.

Thanks in advance
Harribone

[Harribone]

Managed to figure it out eventually:

Please Login or Register  to view this content.