Defining a print area in a macro

[Steven Nairn]

I am using Excel 2007.

I need to create a macro which identifies a range of cells as the print
area. The range varies and I need to macro to allow for this.

I need, within the macro, to go to the extreme bottom right hand end of the
data and create a print area which goes from that cell back to A1. The
problem is that the bottom right hand end of the data is not always in the
same place!

Any suggestions?

Many thanks in anticipation.

 

[Dave Peterson]

Isn't this the way excel determines what to print if you don't specify a print
range?

I'd try removing the print range (er, setting it to "") to see if that worked
ok.

If not, maybe you could use row 1 and column A to determine where that bottom
right corner should be.

Dim LastRow as long
dim LastCol as long
dim myRng as range
with worksheets("Somesheetnamehere")
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
set myrng = .range("A1",.cells(lastrow,lastcol))
.PageSetup.PrintArea = myrng.address(external:=true)
end with