Excel Formatting with VBA

[tpreece01]

Hi all, I'm looking for some specific help in formatting a table in Excel.

I have a file with three sheets. The first two (let's call them Sheet1 and Sheet2) are set up identically but contain different data in the tables.

I've set up a macro to copy the top priority data from each of these sheets into Sheet3. The copied rows from Sheet 2 directly underneath those from Sheet1 using "Range("A65536").End(xlUp).Offset(1, 0).Select" before pasting.

I have two requirements that I'm struggling to fatham:

1) I want to draw a border around the new table in Sheet3, but as the number of rows may change each time the macro is run I need to do this on the fly. I'm looking for something along the lines of Range("A1:A65536.End(xlUp)) to do this. Can anyone point me in the right direction to get the selection right?

2) I have set the print settings to fill the page in landscape format. There are ten columns in the table but it might be that only six are filled in, with the final four to the right being blank. I want to have a print macro that finds the last non-empty cell (excluding the row headers) sets the print area from A1:XX.
I don't know where to start on this one!

Any help on either problem will be really appreciated! Thanks in advance.