VBA code for Setting Print Areas for my macro

cbarryb

New Member
Joined
Jun 1, 2012
Messages
18
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
    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

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
try this:
Code:
    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

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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 ?! :ROFLMAO: I wasn't watching over your shoulder, promise.
 

cbarryb

New Member
Joined
Jun 1, 2012
Messages
18
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top