ExcelBanter - Selecting Ranges to print

Here is some code that should work for you. The function LastCell returns the
last cell that has a value in it...

Sub test()
MsgBox LastCell(Sheets("Sheet1")).Address
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson

"Needhelp" wrote:

I have a file with several worksheets all having formulas from row 8 to row
78. I would like to be able create a macro that sets the print range to end
on the row where the last formula has retrieved data on each worksheet. For
example, sheet1 may end on row 31 while sheet2 ends on row 55, etc. The
retrieved data is unique for each row in column B until the last row is
reached where no data is returned. The formula sets these cells to blank.
any suggestions?