Setting print area to last value in column

[hsingh3]

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!

 

[AtlantaAccessAndExcel]

Here are some code snips to get you headed in the right direction.


This will get you the last row with data, by column:

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:

MyRange = "A1:A" & LastRow_A 
Activesheet.PageSetup.PrintArea = MyRange

 

[hsingh3]

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]

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]

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]

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]

Hi Mark, thanks again for the continued help.

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

 

[AtlantaAccessAndExcel]

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.

 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