Below is the code I am currently using to set the print range, header title,
and put a border around the selected print area. I need some help revising
the code to allow for a variable range. The beginning cell will always be
A1 (1, 1), but the lower left cell that defines the boundary of the print
area will change depending on data that is imported into the worksheet.
Column B will determine the row that will defines the last cell. It should
be two rows below the Cell in Column B that Contains the text "Target %
Comp." The same row will determine will determine the column for the last
cell. It will be one column over from the last cell that contains data on
the row where "Target % Comp." is located. All other cells on this row will
be blank.

For example: Now "Target % Comp." is in Row "41" and the last data is in
Column "AS", by going 2 rows down and one row to the left the last cell
should be AT43 as listed in the code below.


Sub SetPrintRange()
With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = "$A$1:$AT$43"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
Range("$A$1:AT43").BorderAround Weight:=xlThin
End With
End Sub