Print area always changes - SOLVED

[Mr_Adams]

I use the code below to print a portion of a sheet. My problem is that the area always changes

The $A$476 should be the first cell in that column that contains "HEA"

The "536" in $F$536 is the last row that contains data.

There are conditional formats all the way down to row 1,000 but not data

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
<SPAN style="color:#00007F">Dim</SPAN> strCurrentPrinter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strNetworkPrinter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF")
    <SPAN style="color:#00007F">If</SPAN> Len(strNetworkPrinter) > 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' found the network printer</SPAN>
        strCurrentPrinter = Application.ActivePrinter
        <SPAN style="color:#007F00">' change to the network printer</SPAN>
        Application.ActivePrinter = strNetworkPrinter
        ActiveSheet.PageSetup.PrintArea = "$A$476:$F$536"
        ActiveSheet.PrintOut   <SPAN style="color:#007F00">'print something</SPAN>
        <SPAN style="color:#007F00">' change back to the previously active printer</SPAN>
        Application.ActivePrinter = strCurrentPrinter
        ActiveSheet.PageSetup.PrintArea = ""
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Obviously there is a function also that finds the full network printer name, I didn't think it was important, because my issue is the print area. Everything else works fine. If you need it let me know and I will post it

Thanks in advance

 

[Glove_Man]

There's a number of ways - it sort of depends on what else is in your sheet.

You can actually define the print area to be a dynamic range, that way you don't need to assign the print area, you just print it.

Something like Insert | Name | Define

Make the name PrintArea equal to the range

=OFFSET($A$476,0,0,COUNT($A$476:$A$1000),6)

Philip

 

[Legacy 51064]

try:

Dim Top, LastRow as Long
Top = Range("A:A").Find(What:="HEA").Row
LastRow = Range("F65536").End(xlUp).Row

'Sets the Print Area
ActiveSheet.PageSetup.PrintArea = Range("A" & Top, "F" & LastRow)

 

[Glove_Man]

Sorry, if the HEA is also variable (but represents the full cell) you'll need to use something more like:

=OFFSET($A$476,MATCH("HEA",$A$1:$A$100,0),0,COUNT($A$476:$A$1000),6)

 

[Mr_Adams]

98illini,
Its is telling me "unable to set the PrintArea property of the PageSetup class"

ActiveSheet.PageSetup.PrintArea = Range("A" & Top, "F" & LastRow)

It is finding the correct Top and LastRow, it just can't seem to set the printarea.

Glove_Man
I have tried the dynamic range approach. problem is that every month the data in the sheet is removed and replaced with new data so the range can vary widely from month to month. The three things that will always remain constant is that "HEA" will always appear in column A for the first row of the print area and the last row of data will always be the last row of the print area and the columns will always include A-F.

 

[Mr_Adams]

Print Area Always Changes - SOLVED

OK I figured it out.

I can't seem to use Range to set the PrintArea.

Change

ActiveSheet.PageSetup.PrintArea = Range("A" & Top, "F" & LastRow)

To

ActiveSheet.PageSetup.PrintArea = "A" & Top & ":" & "K" & LastRow