Setting print area to last value in column

hsingh3

New Member
Joined
Jul 27, 2016
Messages
4
Hi all,

I'm trying to create a print macro that would set the print area up to the last value in a specific cell.

For example, in column A, if the last value is in cell A145 then the print area will be A1 to A145.

This method will loop for the next 9 columns (A to J) if they have values.

To clarify, if column A has values up till A145 and column B has values up until B75 then the print area will be set so the first page will print the values in column A till A145 and on the next page it will print column B till cell B75. If there no values in column C and after nothing will be printed.

Thank you in advance!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

AtlantaAccessAndExcel

Board Regular
Joined
Mar 14, 2016
Messages
85
Here are some code snips to get you headed in the right direction.


This will get you the last row with data, by column:
Code:
LastRow_A = Cells(Rows.Count, "A").End(xlUp).Row
LastRow_B = Cells(Rows.Count, "B").End(xlUp).Row

This will programmatically set the print area:
Code:
MyRange = "A1:A" & LastRow_A 
Activesheet.PageSetup.PrintArea = MyRange
 

hsingh3

New Member
Joined
Jul 27, 2016
Messages
4
Thank you for your response.

The code that you provided does exactly what I'm looking to do. However, how can I loop this so it does the same action for the adjacent columns while also adding to the print area?

I tried playing around with the code a little bit but I haven't been able to add to the print area accordingly.
 

AtlantaAccessAndExcel

Board Regular
Joined
Mar 14, 2016
Messages
85
What is your target end result? For example, if each column had 5 rows would the output be 10 pages, each with one column's worth of data?
 

hsingh3

New Member
Joined
Jul 27, 2016
Messages
4

ADVERTISEMENT

Yes that is the intended end result. Also, to clarify, if there is no data in column C then only the data in column A and B will be printed.
 

AtlantaAccessAndExcel

Board Regular
Joined
Mar 14, 2016
Messages
85
OK. Got it.

You actually need to head in a different direction. What you are needing to do is insert vertical page breaks between the columns. Your print area would need to be the entire area that has data (which will be the default if you don't set a print area).

You would loop through each column and insert a break. If a column was blank you wanted to skip printing it, you could programmatically hide the column.

If you need more help, I can post code later today.

Mark
 

hsingh3

New Member
Joined
Jul 27, 2016
Messages
4
Hi Mark, thanks again for the continued help.

If you can post some code as an example that would be extremely helpful.
 

AtlantaAccessAndExcel

Board Regular
Joined
Mar 14, 2016
Messages
85
This should work.

This assumes that you are not 'skipping' any columns. In other words, if there is no data in "C" there is also no data after "C". If that is not the case, you will need to hide the empty columns.

Code:
 Sub SetUpPageForPrint()

'Reset the print area
ActiveSheet.PageSetup.PrintArea = ""


'insert page breaks
ActiveSheet.VPageBreaks.Add Before:=Range("B1")
ActiveSheet.VPageBreaks.Add Before:=Range("C1")
ActiveSheet.VPageBreaks.Add Before:=Range("D1")
ActiveSheet.VPageBreaks.Add Before:=Range("E1")
ActiveSheet.VPageBreaks.Add Before:=Range("F1")
ActiveSheet.VPageBreaks.Add Before:=Range("G1")
ActiveSheet.VPageBreaks.Add Before:=Range("H1")
ActiveSheet.VPageBreaks.Add Before:=Range("I1")
ActiveSheet.VPageBreaks.Add Before:=Range("J1")




End Sub


Mark
 

Forum statistics

Threads
1,143,637
Messages
5,719,969
Members
422,252
Latest member
wannabegeek1

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