macro with print area

[kelvin_9]

hello, i would like to figure out the code of print area
i'm not sure how can i start with B22 and end with XXX in my sheet?
should i use this code with ending: row = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row?

PS: XXX is not same as always.

thanks in advance

 

[Declamatory]

Is XXX the last row? If so maybe

    LastRow = ActiveSheet.Columns("B:B").Find(What:="*" _
    , After:=[B22], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveSheet.PageSetup.PrintArea = "$B$22:$B$" & LastRow

 

[kelvin_9]

Is XXX the last row? If so maybe

    LastRow = ActiveSheet.Columns("B:B").Find(What:="*" _
    , After:=[B22], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveSheet.PageSetup.PrintArea = "$B$22:$B$" & LastRow

yes, XXX = last row
sometime might: b22:T50, b22:t55, b22:r30.......

 

[Declamatory]

Do all of the cells in the range have data in them?

 

[kelvin_9]

yes.
actually, it's a daily roster i have to print out everyday. however it's not same as always, so i would like to set a code to run my roster and print area.

 

[MARK858]

Try untested...

 LastRow = Cells.Find(What:="XXX" _
    , After:=[B22], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.PageSetup.PrintArea = Range("B22:B" & LastRow).Address

Change the single B to your last column

 

[Declamatory]

The last column isn't known.

If all of your cells have data in them then the following will work. If there are blanks on row 22 or in column B then it won't work.

    Range("B22").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.PageSetup.PrintArea = Selection.Address

 

[MARK858]

Now I am by a computer, if the last column isn't known then the code below will deal with the possibility of empty cells and doesn't use selects.

Sub XXXX()
    Dim LastRow As Long, LastCol As Long

    LastRow = Cells.Find(What:="*" _
                               , After:=Range("B" & Rows.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    LastCol = Rows("22:" & LastRow).Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column

    ActiveSheet.PageSetup.PrintArea = Range(Cells(22, "B"), Cells(LastRow, LastCol)).Address
End Sub

 

[MARK858]

Or a little shorter and allowing for B not being the longest column (although in this case it is by what the OP has stated).

Sub XXXX()
    Dim LastRow As Long, LastCol As Long

    LastRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    
    LastCol = Rows("22:" & LastRow).Find("*", , , , xlByColumns, xlPrevious).Column

    ActiveSheet.PageSetup.PrintArea = Range(Cells(22, "B"), Cells(LastRow, LastCol)).Address
End Sub