Pretty sure this is straight forward but not quite sure which vba
function is best for this task but essentially I want to format a
sheet of data that has a fixed number of columns but a variable number
of rows.

Therefore I need a macros that can count the number of populated rows
in the first column then to simply apply a grid overlay and set the
print area to that last row, any ideas?

You can get the row number of the last row containing any data (no matter
what column that data is in) using this...

LastUsedRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row

--
Rick (MVP - Excel)


wrote in message
...
Pretty sure this is straight forward but not quite sure which vba
function is best for this task but essentially I want to format a
sheet of data that has a fixed number of columns but a variable number
of rows.

Therefore I need a macros that can count the number of populated rows
in the first column then to simply apply a grid overlay and set the
print area to that last row, any ideas?

Any blank rows or columns?

If not, setting the current region should work.

Dim rng As Range
Set rng = Cells(1).CurrentRegion
do stuff with rng


Gord Dibben MS Excel MVP


On Mon, 16 Mar 2009 13:45:32 -0700 (PDT), "
wrote:

Pretty sure this is straight forward but not quite sure which vba
function is best for this task but essentially I want to format a
sheet of data that has a fixed number of columns but a variable number
of rows.

Therefore I need a macros that can count the number of populated rows
in the first column then to simply apply a grid overlay and set the
print area to that last row, any ideas?

Dim myRng As Range

With Worksheets("Sheet99") 'sheet name changed here
'I'm using columns A:X and the last row in column A
Set myRng = .Range("a1:x" & .Cells(.Rows.Count, "A").End(xlUp).Row)
.PageSetup.PrintArea = myRng.Address(external:=True)
End With

There are tons of ways to apply that border formatting. I'd record a macro the
way I wanted (border styles, colors, weights, ...) and then apply it the myRng
range object.

If you need help with that, post back with your recorded code.

I'm sure you'll get lots of help.

" wrote:

Pretty sure this is straight forward but not quite sure which vba
function is best for this task but essentially I want to format a
sheet of data that has a fixed number of columns but a variable number
of rows.

Therefore I need a macros that can count the number of populated rows
in the first column then to simply apply a grid overlay and set the
print area to that last row, any ideas?

--

Dave Peterson