Help recording a macro to define print area

[John]

(Posted also under Excel "crash" section)

I recorded a macro (relative cell reference)
by finding a certain cell by a "find" criterion,
and then shading it with a shift-end-left arrow
and a shift-end-up arrow. I then set this as the print
area and printed it. So far so good. Before finishing the
macro I deleted the Print area from the name box
so that the spreadsheet would be exactly the same
after I cleared the contents, and ready to use again.

When I used a different file with a different number
of records and tried the use the macro, the print
job included only the number of rows in the original
macro. ( Remember I did use a relative reference when
recording). When I looked at the Visual Basic
I saw that the macro recorder recorded the static
cell addresses for the print area (A1:F12, for example).

I thought next, that if I range-named the block before
setting it as the print area that that might work.
No luck.

Is there a trick to shading an area while recording a
relative cell reference macro so that the print area
confoms to the newly defined (shaded) area each time?

Suggestions Welcome, Solutions Moreso.

Thanks

 

[kkknie]

I'm assuming you recorded a find to get something like:

Cells.Find(What:="a", After:=ActiveCell, LookIn:=xlFormulas
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
MatchCase:=False _
, SearchFormat:=False).Activate

If so, first change the .Activate to .Select

Assuming you wanted to select the found row plus the next 11 rows an
the found column plus the next 5 columns, use the following line o
code to make the selection which would be used to set your print area.

Range(Selection, Cells(Selection.Row + 11, Selection.Column
5)).Select

Modify to meet your needs.