VBA code - Set Print Area based on last row data

[Spaztic]

Hi, I'm looking to Set Print Area from Row 1 to the last Row where there is data (under column B). However, there is a twist. (And if the twist isn't easily done, just finding the last row where data is entered under column B will be fine)

If the last entered data is the word "Display", the Print Area should be from Row 1 and then 3 rows up from the word 'Display' (Row 10 in the example below)

If the last entered data is NOT the word "Display", the Print Area should be from Row 1 to that last entered data row (Row 15 in the example below)

 

[Joe4]

Try this:

Sub SetPrintRange()

    Dim lr As Long
    Dim rng As Range
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Set print range
    If Cells(lr, "A").Value = "Display" Then
        Set rng = Range("A1:A" & lr - 3)
    Else
        Set rng = Range("A1:A" & lr)
    End If
    
'   Set print area
    ActiveSheet.PageSetup.PrintArea = rng.Address
    
End Sub

 

[Spaztic]

It works! Thank you so much!

 

[Joe4]

You are welcome!
Glad I was able to help.