protected worksheet
 

I have prepared a worksheet for user entry in which there are formulas.
The worksheet is protected and I am trying to have interim reports printed
using a
macro. When the current region is selected in the macro, the entire area
including
empty cells with the formulas copied, is selected. How do I get the print
area to
stop at the point where data has been entered, excluding the empty,
formatted rows.

protected worksheet
 

Say your current region range is Rg and it is set.
Let's call rgStart, rgEnd as range for first and last cells of the table to
be printed.
Dim rgStart as Range, rgEnd as range

-Start Cell
set rgStart = Rg.cells(1)
- last Row
Now, do you know any column where the last the last non-empty cell would
define the last row of the table to be printed?
Say column E is such a column:
Dim rg1
Set rg1 = Rg.parent.Range("E65536").End(xlUp)
- End cell:
It is at the intesection of rg1's row and rg's last column:
set rgEnd = application.intersect(Rg.Cells(Rg.Cells.Count).Ent ireColumn ,
rg1.EntireRow)
- Finally the new range to print, Rg :
Set Rg=Range(rgStart, rgEnd)

Regards,
Sebastien

"LennyG" wrote:

I have prepared a worksheet for user entry in which there are formulas.
The worksheet is protected and I am trying to have interim reports printed
using a
macro. When the current region is selected in the macro, the entire area
including
empty cells with the formulas copied, is selected. How do I get the print
area to
stop at the point where data has been entered, excluding the empty,
formatted rows.