Formatting Macro using rowcount

[mematebigdave]

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?

 

[Rick Rothstein]

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

 

[Gord Dibben]

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

 

[Dave Peterson]

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.