VBA Code to Set Print Area Automatically

[chaddug]

Hello, I need just a pretty simple vba code, but I cant find one that already posted that does both things I want, and my VBA is not good enough to combine what I have found.

All I want is a code that will set the print area width to be from columns A:L and the height to the last non-blank cell.

I found a code that sets the print area to the all of the non blank cells but it doesn't include column L because it is blank, but for printing reasons that column must be included.

Also if the code could set the bottom border of the last non-blank cell to be a think black border that would be great!

Thanks much!

[gyclone]

To get the last row, you can use the "UsedRange" method:

dim lastrow as long
lastrow = worksheets("Sheet1").UsedRange.rows(worksheets("Sheet1").UsedRange.rows.count)

Note, though, that UsedRange will include any formatted cells, whether or not they contain data. Therefore, I usually use a different method instead. Assuming Column A has values in every row or your table, you can do this:

dim lastrow as long
lastrow = worksheets("Sheet1").range("A1048576").End(xlUp).row

Now that you know the last row, you can set your range like this:

dim rng as range
set rng = Worksheets("Sheet1").Range("A1:L"& lastrow)
Worksheets("Sheet1").pagesetup.PrintArea = rng

Just replace "Sheet1" with the appropriate name for your sheet.

[gyclone]

Oh, and to add a thick border to the whole last row, you can do this:

With Rng.Borders(xlEdgeBottom)
        .LineStyle = XlLineStyle.xlContinuous
        .Weight = xlMedium
    End With

If you really just want the border on the last cell, you could do like this:

dim lastcolumn as long
lastcolumn = worksheets("Sheet1").Range("AZ" & lastrow).end(xlToLeft).column
with rng.cells(lastrow, lastcolumn).borders(xlEdgeBottom)
.LineStyle = xlLineStyle.xlContiuous
.Weight = xlMedium
end with