Problems with Macro's to set print Range

[Sgtbillcoe]

Hi

I have been having a problem trying to create a macro that sets the print area of my document by looking for the text Grand Total in Column A and the text Grand Total in Row 7. The sheet I will be using this on is created by an export from our server.

Any Help would be greatly appreciated

 

[GlennUK]

You could loop through the relevant cells? Or use the WorksheetFunction Match to find the relevant row and column? Or the Find command?

Here's one way ( without error checking ):

    mywidth = Application.WorksheetFunction.Match("Grand Total", Range("7:7"), 0)
    mylength = Application.WorksheetFunction.Match("Grand Total", Range("A:A"), 0)
    ActiveSheet.PageSetup.PrintArea = Range(Range("A7"), Cells(mylength, mywidth)).Address