Macro to print a dynamic range

[Guest]

I appreciate any insights you experts can give to me.

I want to use multiple command buttons on a single worksheet to print
associated subsets (ranges) of worksheet data that may vary. In each case I
would like to search the worksheet for "planted" text strings (that will
indicate the upper-left and lower-right limits of a desired print range) and
return those locations into the
ActiveSheet.PageSetup.PrintArea = "loc upper-left:loc lower-right"
function.

Can this be accomplished? Thanks

 

[Guest]

A couple of ideas come to mind:
1. Create dynamic ranges, say MyRange1,...MyRange2 whatever:
Insert>Name>Define Enter MyRange1 in the Name box.
Below in the RefersTo box enter (with the equal sign)

=Offset(Sheet1!$B$2,0,0,CountA(Sheet1!$B$2:$B$1000),1)

The 1st argument is the upper left cell of you range.
The 1000 is beyond the max rows you will ever expect you will need.
The last 1 is the number of columns in your range - its width.
As you add/delete data to the lower end of your range or insert rows, the
range definition changes to accomodate the data

Then you can simply print this range with:
ActiveSheet.PageSetup.PrintArea = MyRange1.Address

Does this help?

 

[Don Lloyd]

Hi,

The following may help to get you going.
Assuming that your text string is in B4 - I assume you will know how to find
the cell containing the string.

Sub AAATest()
Dim Parea
Parea = Range("B4")'or Cells(4, 2)
ActiveSheet.PageSetup.PrintArea = Parea
End Sub

regards,
Don

 

[Guest]

gocush, This helps but I am unclear on whether this technique will return
the location of the two cells of interest.

Does Excel have a function that will return the "alpha:numeric" vector of a
cell that contains a specified value? If so, I could search for that value
and return the vector into the "PrintArea...." function.