Macro to Border and Select Print Area for Queried Data

[rickincanada]

I have a workbook that queries a SQL database and returns a different number of records each time. The query populates data from columns A thorugh J, however I'd also like to have an additional blank column print as well. My challenge is that I want to include this additional column, in this case K, when adding a border and setting my print area via a macro - can anyone offer me an idea of how to do this?

Thanks so much!

 

[MrKowz]

Something like this?

Public Sub PrintAreaSet()
Dim LR  As Long, _
    LC  As Long
    
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1
With Range(Cells(1, 1), Cells(LR, LC))
    .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=xlColorIndexAutomatic
    .PageSetup.PrintArea = .Address
End With
End Sub

 

[rickincanada]

This only outlines as far as J and I'm getting the following error:

Run-time error '438'
Object doesn't support this proprerty or method

The item it highlights in debug is .PageSetup.PrintArea = .Address

Excel 2003. Thanks!

 

[MrKowz]

Doh - that'll teach me for not testing code before handing it out... minor error:

Public Sub PrintAreaSet()
Dim LR  As Long, _
    LC  As Long
    
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1
With Range(Cells(1, 1), Cells(LR, LC))
    .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=xlColorIndexAutomatic
    [B][COLOR=red]ActiveSheet[/COLOR][/B].PageSetup.PrintArea = .Address
End With
End Sub

 

[rickincanada]

Print area is good however it's still missing column K

 

[MrKowz]

Do you have data going all the way out to column J in row 1 in your spreadsheet? It works fine on my end.

 

[rickincanada]

No - there is not data in that column - I need a blank line to print information on.

 

[MrKowz]

I see... in that case, try:

Public Sub PrintAreaSet()
Dim LR  As Long, _
    LC  As Long
    
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells([B][COLOR=red]2[/COLOR][/B], Columns.Count).End(xlToLeft).Column + 1
With Range(Cells(1, 1), Cells(LR, LC))
    .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=xlColorIndexAutomatic
    [COLOR=black]ActiveSheet[/COLOR].PageSetup.PrintArea = .Address
End With
End Sub

Basically, it is looking for the last column in the row highlighted in red in my above code. You may need to adjust this number to a row that always has data in it if you have multiple blank rows above your data.

 

[rickincanada]

That's perfect - the only other thing though is that I want to border all fields entirely, not just around. I know - this must be painful...

Thank you!

 

[MrKowz]

I know - this must be painful...

Not painful at all! You gave a good thread title and a short, but detailed, description. Those kinds of posts are always a sight for sore eyes!

Public Sub PrintAreaSet()
Dim LR  As Long, _
    LC  As Long
    
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1
With Range(Cells(1, 1), Cells(LR, LC))
    .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=xlColorIndexAutomatic
    With .Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    ActiveSheet.PageSetup.PrintArea = .Address
End With
End Sub