I have a worksheet that when an existing macro is run on selected criteria
the data may contain a differing numbers of rows, eg data may finish at row
90 or row 100. The final row always contains the same data in column A.

How can I set the print area to be aligned with the final row?

Cheers

Try using this Macro/routine by changing the word "Test" to whatever the
final row data in column A always is...

Sub PrintRange()
Dim c As Range
For Each c In Range(Range("A1"), Range("A65536").End(xlUp))
If c.Value = "Test" Then
Range("A1", c.Offset(0, 0).Address).Name = "Print_Area"
Exit For
End If
Next
End Sub



"eagles1994" wrote:

I have a worksheet that when an existing macro is run on selected criteria
the data may contain a differing numbers of rows, eg data may finish at row
90 or row 100. The final row always contains the same data in column A.

How can I set the print area to be aligned with the final row?

Cheers

A non-macro approach:

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) )

(Make that 1000 big enough to extend past the last possible row.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).


eagles1994 wrote:

I have a worksheet that when an existing macro is run on selected criteria
the data may contain a differing numbers of rows, eg data may finish at row
90 or row 100. The final row always contains the same data in column A.

How can I set the print area to be aligned with the final row?

Cheers

--

Dave Peterson

And if you need code, you could use something like:

Option Explicit
Sub testme01()
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.PageSetup.PrintArea = "$A$1:$E" & LastRow
End With

End Sub

Change $E to the column that you want.

eagles1994 wrote:

I have a worksheet that when an existing macro is run on selected criteria
the data may contain a differing numbers of rows, eg data may finish at row
90 or row 100. The final row always contains the same data in column A.

How can I set the print area to be aligned with the final row?

Cheers

--

Dave Peterson

Thanks for that guys. Works a treat!

Cheers