VBA code for Setting Print Areas for my macro

[cbarryb]

Hi,

I have a macro already setup to do most of the tasks, but what I need to is be able to select the area from cells D1:M1 down to the bottom of my data, which changes every day, it could be 100 rows down or possibly 300rows. Is there a way to do this for themacro? To select the varying data range, then set print area to that.

 

[Joe4]

Welcome to the Board!

Can we use column D to determine where the last row of data is? If so, this block of code will do what you want:

    Dim lr As Long
    
'   Find last row in column D with data
    lr = Cells(Rows.Count, "[COLOR=#ff0000][B]D[/B][/COLOR]").End(xlUp).Row


'   Set print area
    ActiveSheet.PageSetup.PrintArea = "$D$1:$M$" & lr

If some other column would be better to use, then just replace the red D in the code above with that column letter.

 

[bobsan42]

try this:

    Dim i As Long
    With ActiveSheet
        i = .Range("[COLOR=#ff0000]D[/COLOR]" & .Rows.Count).End(xlUp).row
        .Range("D1:M" & i).Select
        .PageSetup.PrintArea = .Range("D1:M" & i).Address
    End With

if you expect to have blank cells in column D, replace the red D with a column, which you expect to be always full (no blank cells) - just to be sure that no data will be omitted.

 

[bobsan42]

Welcome to the Board!

Can we use column D to determine where the last row of data is? If so, this block of code will do what you want:
....

REALLY ?! I wasn't watching over your shoulder, promise.

 

[cbarryb]

Yes, the data on the sheet runs in all rows through columns A to M, and there are no blank cells, but when printing the sheet, I only need the data from columns D to M, but don't want endless printing of blank pages thanks guys, I'll have to put this in to action on Mon at work, but will play around around in a dummy sheet over the weekend.