Macro to set print area

[bmt216a]

Hello everyone, I want to create a macro that will set the print area when the rows filled with data are always changing. Eg. One day data may fill 20 rows, another day may fill 90 rows. I need to set print area from columns A-D down to an undetermined row that is always changing based on how much data I have that day. The first row to print will always be 1 and the last row will be the one with data. Thank you for you help.

 

[DanteAmor]

Use this:

Sub SetPrintArea()
  Dim lr As Long
  lr = Range("A:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & lr
End Sub

 

[bmt216a]

Use this:

Sub SetPrintArea()
  Dim lr As Long
  lr = Range("A:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & lr
End Sub

Thank you, I've just tried it and it doesn't seem to be doing what I want. I have attached a screenshot of the print preview here to show you what I mean. I don't want any additional rows printed after the "CC" row as that is where the data stops, but it is still showing those rows to be printed. I want the macro to dynamically adjust the print area based on where the last row with data is located and not print anything else after.

 

[DanteAmor]

I've just tried it and it doesn't seem to be doing what I want.

lr = Range("A:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row

That line tests for the last cell with data in columns A, B, C, and D. That was your original request:

I need to set print area from columns A-D

In your image the columns are not visible, but I assume that the first column is column A, so you must adjust the line that evaluates the last cell with data to this:

Sub SetPrintArea()
  Dim lr As Long
  lr = Range("B:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & lr
End Sub

I am only evaluating columns B and C, because in your image I can see that in column D you have zeros, those cells are not empty.

 

[bmt216a]

That line tests for the last cell with data in columns A, B, C, and D. That was your original request:


In your image the columns are not visible, but I assume that the first column is column A, so you must adjust the line that evaluates the last cell with data to this:

Sub SetPrintArea()
  Dim lr As Long
  lr = Range("B:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & lr
End Sub

I am only evaluating columns B and C, because in your image I can see that in column D you have zeros, those cells are not empty.

I should have been more clear. You are correct columns B and C are being evaluated. As for column D, there is a simple formula in every other cell that calculates the difference between two values in column B. Your code did the trick. Thank you so much for your help. If you don't mind, could you explain a little about how you got to this solution? Thank you